Named range in workseet code

[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.

 

[Mike H]

Dave,

It's no different to referring to a cell, if you don't specify the worksheet
the activesheet is assumed but in this case there is no range with that name
on the activesheet so it falls over. Try this

Private Sub Worksheet_Activate()
With Sheets("Sheet2").Range("CompNames")
MsgBox .Address
End With
End Sub


Mike

 

[Jacob Skaria]

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

 

[Dave]

Hi Mike,
I tried your suggestion of:
With Sheets("Sheet2").Range("CompNames")
and it works! I'm sure I tried that before.
However, I thought that one of the features of named ranges was that you
didn't have to name the sheet. Also, it doesn't solve the mystery as to why
'With [CompNames]' works without any reference to a sheet name.
Regards - Dave.

Dave,

It's no different to referring to a cell, if you don't specify the worksheet
the activesheet is assumed but in this case there is no range with that name
on the activesheet so it falls over. Try this

Private Sub Worksheet_Activate()
With Sheets("Sheet2").Range("CompNames")
MsgBox .Address
End With
End Sub


Mike

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.

 

[Dave]

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.

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

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.

 

[Patrick Molloy]

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.

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.

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

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.

 

[Dave Peterson]

Unqualified objects (like range()) will belong to the activesheet if the code is
in a General module.

But unqualified objects in a worksheet module will belong to the sheet that owns
the code.

So
with range("compnames")
is like writing
with me.range("compames")

and Me (the object (sheet in this case) owning the code) doesn't have a range
named Compnames.

Using [] goes back to excel to evaluate the stuff inside the brackets. This may
be quicker to type, but it's actually slower than using range().

ps.

It's always a good thing to qualify your ranges--no matter where the code is
located.

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.

 

[Dave]

Mr Peterson,
Thank-you for taking the trouble to read and understand what I really wanted
to know. You have picked up all my questions, and answered them. Impressive!
Regards - Dave.

Unqualified objects (like range()) will belong to the activesheet if the code is
in a General module.

But unqualified objects in a worksheet module will belong to the sheet that owns
the code.

So
with range("compnames")
is like writing
with me.range("compames")

and Me (the object (sheet in this case) owning the code) doesn't have a range
named Compnames.

Using [] goes back to excel to evaluate the stuff inside the brackets. This may
be quicker to type, but it's actually slower than using range().

ps.

It's always a good thing to qualify your ranges--no matter where the code is
located.

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.

 

[Dave]

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.

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.

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.

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


:

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.

.