VBA Referencing a Named Cell Range in another Workbook
Frank,
Interesting that. I have never tried it myself.
One problem with it is that it doesn't work if the name doesn't already
exist. I use this myself
ActiveWorkbook.Names.Add Name:="UserTotal2", RefersTo:="=$A$1"
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Frank & Pam Hayes" wrote in message
...
Thanks Bob,
Your suggestion worked. In addition, it allowed me to set the value in
the
Named Cell by using
Evaluate(Workbook1.Names("UserTotal").RefersTo) = 500
Thanks,
Frank
"Bob Phillips" wrote in message
...
Frank,
I guess this is because the range is a property of woeksheets, not
workbooks.
You could try
Evaluate(Workbook1.Names("UserTotal").RefersTo)
--
HTH
Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)
"Frank & Pam Hayes" wrote in message
...
I have a VBA application (in Workbook2) that reads and changes
information
in a Named Cell in a user's Workbook (WorkBook1). The name
"UserTotal"
has
been Insert/Name/Define defined in Workbook1 as =Sheet1!$A$1.
The VBA code works fine whey I explicitly reference the sheet name in
Workbook1 that the Name is defined as, but does not work if I only
refer
to
Workbook 2. Since different users may have defined "MyTotal" on
different
sheets, is there a way of referring to the Named Cell without being
explicit
as to what cell it is on, something like:
MyValue = Workbook1.Range(UserTotal).value
instead of
MyValue = Workbook1.Sheet1.Range(UserTotal).value
or lacking that, is there a simple way of setting another variable
equal
to
the Wookbook.Worksheet name that a variable is defined as in a way
that
I
can use that in th code line above?
Any help would be appreciated.
Thanks,
Frank Hayes
|