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



.