Hi Patrick,
If the code is in a normal module, yes.
But if it's in a worksheet module, no.
Dave Peterson's answer explains this well.
Regards - Dave.

"Patrick Molloy" wrote:

excel 2003
new workbook with three sheets
select sheet2
name a cell TEST and give it a value
select sheet1
in code

Sub check()
MsgBox Range("TEST")
End Sub

this works irrespective of which sheet it active
though I'm sure it shouldn't.

"Dave" wrote in message
...
Hi Jacob,
I tried 'With ThisWorkbook.Names("CompNames")'
and it also works, without sloving the mystery of why
'With [CompNames]' works, but 'With Range("CompNames")' doesn't.
Thanks for your input.
Regards - Dave.

"Jacob Skaria" wrote:

Dave; try

Private Sub Worksheet_Activate()
With ThisWorkbook.Names("CompNames")
MsgBox .RefersTo
End With
End Sub

or use the Workbook SheetActivate Event

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
With Range("CompNames")
MsgBox .Address(External:=True)
End With
End Sub


--
Jacob


"Dave" wrote:

Hi
I am using XL2003
This is a follow-on from a previous post.
I have the following (meaningless) Worksheet Window macro:

Private Sub Worksheet_Activate()
With Range("CompNames")
End With
End Sub

If the named range (CompNames) is on the sheet being activated, the
code
runs ok.
If the named range is on a sheet other than the one being activated, I
get
the following error:

Runtime error '1004'
Application-defined or object-defined error

If the code is put into a normal macro in a module, the error does not
occur.

Sub Test()
With Range("CompNames")
End With
End Sub

I have discovered that I can get around this by using:
Private Sub Worksheet_Activate()
With [CompNames]
End With
End Sub

But I can't find any reference to this style of notation in the Help,
nor do
I know why it works when the other doesn't, or if there are any
limitations
of this syntax. Someone in another group mentioned it, and I tried it
out of
desperation.
Needless to say, I have a slightly more useful macro in mind, but I
have
thinned it down to this to try and solve the thing.
Has anyone else had this problem, or can anyone else confirm that this
is
so? It's been driving me nuts.

Regards - Dave.

.