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.