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

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

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

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 <g...@somewhere.net> 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 -