Referering sheet name of a named cell

[Guest]

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.

 

[Bob Phillips]

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)