Sub testbsrange()
Dim oname As Object
Dim rng as Range
For Each oname In worksheets("Blank_Sheet1").parent.Names
set rng = Nothing
on Error Resume Next
set rng = oname.RefersToRange
On Error goto 0
if not rng is nothing then
if lcase(rng.parent.name) = "blank_sheet1" then
MsgBox oname.Name & oname.RefersToR1C1
end if
End if
Next oname
End Sub

--
Regards,
Tom Ogilvy

"LAF " wrote in message
...
I am trying to find all range names that exist on a given sheet in my
workbook. I do not need to know the names for all of the sheets. Here
is the code I used, but it only displayed 1 of the names on that sheet,
when in fact there are about 5. Can anyone help?

Sub testbsrange()
Dim oname As Object
For Each oname In Worksheets("Blank_Sheet1").Names
MsgBox oname.Name & oname.RefersToR1C1

Next oname
End Sub


---
Message posted from http://www.ExcelForum.com/