View Single Post
  #8   Report Post  
Posted to microsoft.public.excel.programming
Stefi Stefi is offline
external usenet poster
 
Posts: 2,646
Default Refer to a named range

Thanks Dave for the explanation, I didn't realized that being in a worksheet
module may cause this problem, now I know what should I focus on when
encountering this problem again.

--
Regards!
Stefi



Dave Peterson ezt *rta:

Just to add to Stefi's advice:

Since you're in the worksheet module (using the worksheet_activate event), an
unqualified range like:

With Range("CompNames")
will refer to the range named CompNames on the worksheet that owns the code.

And your compnames range is on a different sheet.

with worksheets("someothersheetnamehere".range("compnam es")
or even specify the workbook:
with thisworkbook.worksheets("someothersheetnamehere".r ange("compnames")

You could go through the workbook's name collection, too:

with thisworkbook.names("CompNames").referstorange

====================

Ps. I would qualify all the ranges in the code--and I wouldn't use the
Activesheet:

Private Sub Worksheet_Activate()
with thisworkbook.worksheets("Names".range("compnames")
'Me refers to the sheet that owns the code
Set c = .Find(Me.Range("A1"))
'I like the block if/end if
If Not c Is Nothing Then
A = thisworkbook.Sheets("Names").Cells(c.Row, 3)
end if
End With
If me.Name < A Then me.Name = A
End Sub

Stefi wrote:

Try to add worksheet object qualifier with sheet name hosting the named range
like this:

Worksheets("hostsheet_name").Range("CompNames")

I don't know why, but I had cases when this workaround worked.

--
Regards!
Stefi

âžDave❠ezt Ã*rta:

Hi Jacob,
Your line of code runs ok, returning the name of the active workbook. I've
checked the spelling, even pasted it straight from the name box into the
code. As I said, [CompNames] works, but Range("CompNames") doesn't, or any
variant on that I could think of.
Dave.

"Jacob Skaria" wrote:

Check out the exact name of your named range...It seems it is not present in
your active workbook.. Try the below code which should return the
workbookname if the named range is present in the active workbook.

Activeworkbook.Names("Compnames").Parent.name

--
Jacob


"Dave" wrote:

Hi Jacob,
When I use: With Range("CompNames"), I get:

Runtime error '1004':
Method 'Range' of object '_Worksheet' failed

Regards - Dave.

"Jacob Skaria" wrote:

This works

With Range("CompNames")
Set c = .Find(Range("A1"))


End With

--
Jacob


"Dave" wrote:

Hi
XL2003
I am having trouble using a named range in a macro, even after refering the
Help.
The named range lives in the active workbook on another sheet.
My code is this:

Private Sub Worksheet_Activate()
With [CompNames]
Set c = .Find(Range("A1"))
If Not c Is Nothing Then A = Sheets("Names").Cells(c.Row, 3)
End With
If ActiveSheet.Name < A Then ActiveSheet.Name = A
End Sub

You will see that I have had to resort to using [ ] around the named range,
which is the only way I could get the code to run.
I tried Range("CompNames") and Range(CompNames) and tried including the
sheet name and the workbook name, but none of those worked.
What am I doing wrong?
Regards - Dave.


--

Dave Peterson
.