Named range in workseet code

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

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
 
J

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
 
D

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.

Mike H said:
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

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

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.

Jacob Skaria said:
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 said:
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.
 
P

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.

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

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

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.

Dave Peterson said:
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.
 
D

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.

Patrick Molloy said:
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 said:
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 said:
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.

.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top