Referering sheet name of a named cell
 

Hello,
I have a named cell "ADJ_COMMENTS" which is on an other sheet. How can I
know the name of the sheet that contains this cell ?

Dim myRange as Range
On error resume Next
Set myRange = ThisWorkbook.Names("ADJ_COMMENTS").RefersToRange 'is it
better to used Set myRange = Range("ADJ_COMMENTS") ?
If myRange Is Nothing Then
Else
myAdress = myRange.Address 'gives me $A$1 ok
mySheet = myRange. ??? .Application.Sheet.Name doesn't work.

Thank you.
--
Alex St-Pierre

Referering sheet name of a named cell
 

Dim myRange As Range
On Error Resume Next
Set myRange = Range("ADJ_COMMENTS")
If Not myRange Is Nothing Then
myAdress = myRange.Address 'gives me $A$1 ok
mySheet = myRange.Parent.Name
End If


--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Alex St-Pierre" wrote in message
...
Hello,
I have a named cell "ADJ_COMMENTS" which is on an other sheet. How can
I
know the name of the sheet that contains this cell ?

Dim myRange as Range
On error resume Next
Set myRange = ThisWorkbook.Names("ADJ_COMMENTS").RefersToRange 'is it
better to used Set myRange = Range("ADJ_COMMENTS") ?
If myRange Is Nothing Then
Else
myAdress = myRange.Address 'gives me $A$1 ok
mySheet = myRange. ??? .Application.Sheet.Name doesn't work.

Thank you.
--
Alex St-Pierre