D
Dave
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.
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.