VBA Referencing a Named Cell Range in another Workbook
 

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

VBA Referencing a Named Cell Range in another Workbook
 

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

VBA Referencing a Named Cell Range in another Workbook
 

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

-----Original 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



.

VBA Referencing a Named Cell Range in another Workbook
 

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

VBA Referencing a Named Cell Range in another Workbook
 

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

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