[[email protected]]

I'm simply trying to get the text stored in a cell that has its own named range. I keep getting a "Method 'Range' of 'object _Global' failed" error. Here is the code:

Dim temp As String
temp = Range("statusBarMsg").Value2

What can I be missing here? Thanks.

[Peter T[_5_]]

That error might be because the Name doesn't exist, or it doesn't refer to a
range, or it refers to more than one cell, or it has refers to a range
that's been removed (a ref error), or it's a worksheet level name and the
sheet is not active.

How far do you get with this

Dim rng As Range
Dim nm As Name
Set nm = ActiveWorkbook.Names("statusBarMsg")
Set rng = nm.RefersToRange
Debug.Print rng.Address
Debug.Print rng.Value2

Regards,
Peter T


wrote in message
...
I'm simply trying to get the text stored in a cell that has its own named
range. I keep getting a "Method 'Range' of 'object _Global' failed" error.
Here is the code:

Dim temp As String
temp = Range("statusBarMsg").Value2

What can I be missing here? Thanks.

[joeu2004[_2_]]

wrote:
I keep getting a "Method 'Range' of 'object _Global' failed"
error. Here is the code:
Dim temp As String
temp = Range("statusBarMsg").Value2
What can I be missing here?

With Name Manager, take a close look at how statusBarMsg is defined, if at
all. I wonder if the scope is a specific worksheet, not workbook, and when
you execute the line above, ActiveSheet is not worksheet scope defined for
statusBarMsg.

Alternatively, put the following in place of the code above and execute
(just for diagnostic purposes):

Dim nm
For Each nm In ActiveWorkbook.Names
If InStr(nm.Name, "statusBarMsg") Then MsgBox nm.Name
Next

[[email protected]]

On Wednesday, July 11, 2012 2:59:43 PM UTC-6, Peter T wrote:
That error might be because the Name doesn't exist, or it doesn't refer to a
range, or it refers to more than one cell, or it has refers to a range
that's been removed (a ref error), or it's a worksheet level name and the
sheet is not active.

How far do you get with this

Dim rng As Range
Dim nm As Name
Set nm = ActiveWorkbook.Names("statusBarMsg")
Set rng = nm.RefersToRange
Debug.Print rng.Address
Debug.Print rng.Value2

Regards,
Peter T


<
> I'm simply trying to get the text stored in a cell that has its own named
> range. I keep getting a "Method 'Range' of 'object _Global' failed" error.
> Here is the code:
>
> Dim temp As String
> temp = Range("statusBarMsg").Value2
>
> What can I be missing here? Thanks.

Thank you. I actually solved this by simply adding a worksheet reference in front of the range reference. As in sheet1.range("statusBarMsg")...

Thanks for your help. I appreciate it.