Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do I link a constant or variable to form
I created a spreadsheet that retrieves a large data set and based on
options the user selects, the data is reduced and plotted accordingly. Currently, the source data file name and location is hardcoded as a constant into the spreadsheet file to eliminate the complex method required to retrieve the source data (our IT department's doing) . Unfortunately, every few months the source data file name gets changed, (another IT issue) which is OK, as I can replace the constant info in a few seconds. What I'd like to do is enable the userform to display the source data file name at the bottom of the form to alert the user which data set they're using. How do I retrieve a constant from VB code and add it as a label to a form? Art |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do I link a constant or variable to form
It happens that c1802362 formulated :
I created a spreadsheet that retrieves a large data set and based on options the user selects, the data is reduced and plotted accordingly. Currently, the source data file name and location is hardcoded as a constant into the spreadsheet file to eliminate the complex method required to retrieve the source data (our IT department's doing) . Unfortunately, every few months the source data file name gets changed, (another IT issue) which is OK, as I can replace the constant info in a few seconds. What I'd like to do is enable the userform to display the source data file name at the bottom of the form to alert the user which data set they're using. How do I retrieve a constant from VB code and add it as a label to a form? Art Assign the 'Text' of the cell containing the filename info to the 'Caption' property of the label on your userform in the form's Initialize event. Example: (air code) lblDataSource.Caption = Range("DataSource").Text ..where 'lblDataSource' is the name you give to the target label, AND "DataSource" is the local scope name you give to the cell where the filename resides. To give a named range local scope: Select the range In the namebox to left of the Formula Bar, type: '<sheet name'!DataSource ..where <sheet name should be replaced with the actual worksheet name -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do I link a constant or variable to form
GS explained :
To give a named range local scope: Select the range In the namebox to left of the Formula Bar, type: '<sheet name'!DataSource ..where <sheet name should be replaced with the actual worksheet name Be sure to press 'Enter' after typing in the namebox! -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do I link a constant or variable to form
Thanks - it works, although I was hoping to avoid the intermediate
step of writing the filename to a cell before transferring it to the form. Right now the file name is a string constant at the head of the VBA code, but I have a hidden sheet full of info I used per your directions By the way, I had to modify your syntax to: form1.lblDataSource.Caption = Range("DataSource").Text to get an object reference Art On Sep 13, 1:06*pm, GS wrote: Assign the 'Text' of the cell containing the filename info to the 'Caption' property of the label on your userform in the form's Initialize event. * Example: (air code) * * lblDataSource.Caption = Range("DataSource").Text * ..where 'lblDataSource' is the name you give to the target label, AND "DataSource" is the local scope name you give to the cell where the filename resides. * To give a named range local scope: * * Select the range * * In the namebox to left of the Formula Bar, type: * * * '<sheet name'!DataSource * * ..where <sheet name should be replaced with the actual worksheet name -- Garry Free usenet access athttp://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc- Hide quoted text - - Show quoted text - |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
how do I link a constant or variable to form
c1802362 explained on 9/13/2011 :
Thanks - it works, although I was hoping to avoid the intermediate step of writing the filename to a cell before transferring it to the form. Right now the file name is a string constant at the head of the VBA code, but I have a hidden sheet full of info I used per your directions Ok, you could have just used the declared constant instead of the cell text. I didn't know you already had this in place (and so IS WHY I asked you to define the posted variables). According to your explanation, the filename is stored in a cell where users can update it when the filename changes. Not sure why you ALSO hard code it as a CONSTant in code since it's data is variable! By the way, I had to modify your syntax to: form1.lblDataSource.Caption = Range("DataSource").Text to get an object reference This would not be necessary had you put this line of code in the Userform_Initialize event as I instructed you to do. This way, you don't need to ref the userform because it knows itself and all the controls on it. -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc --- Posted via news://freenews.netfront.net/ - Complaints to --- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Getting a constant/variable from another workbook? | Excel Programming | |||
From variable to excel constant | Excel Programming | |||
Removing the constant link from Spinner | Excel Worksheet Functions | |||
Variable Acting Like a Constant? | Excel Programming | |||
Is it possible to open the VBA form with a link in a sheet and to pass variable from a cell to the VBA form? | Excel Programming |