VBA Referencing a Named Cell Range in another Workbook

[Frank & Pam Hayes]

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

 

[Bob Phillips]

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)

 

[Simon Murphy]

Frank
Range is a property of a worksheet not a workbook, which
is why missing out the workshet doesn't work.

I use this:
dim rng as range
set rng = workbook1.names("UserTotal").referstoRange

yourValue = rng.value
etc...


You need to be careful with names as they can be defined
at the workbook level or the worksheet level and you can
have the same name at both levels. This can create hard
to find errors. My advice would be to make the names your
code uses unusual so there is less chance of a clash.

cheers
simon

 

[Frank & Pam Hayes]

Simon,

Thank you for the suggestion. It worked great. However, I am not able to
do it in reverse. How would I write the code to change the value in
"UserTotal" from VBA.

set workbook1.names("UserTotal").value = 500 'did not work

Thanks

Frank

 

[Frank & Pam Hayes]

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]

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)