Excel 2k Local Range Name

[mpezeur]

Have excel 2k workbook with 5 worksheets. Have several ranges defined. On one of the sheets, I'm using vb to search the ranges that apply to the current sheet. If a range(s) applies to that particular sheet, I'd like to check to see if the current cell is within that range (via intersect). Is there a command, or code, that determines if the range name in question is local to that particular worksheet?

 

[SkipVought]

Hi,

This is a perplexing issue. If you insert a Name its .Parent property is the Workbook object. However, if you insert the same name on another sheet, that Name's .Parent property is the Worksheet object and the sheet name is part of the .Name property.

So...

use the .RefersTo property to the LEFT of "!"

Hope this helps

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mpezeur]

I've written this sample code to tell me the names of all range in the workbook and it's parent name:

For i = 1 To ActiveWorkbook.Names.Count
MsgBox ActiveWorkbook.Names(i).Name & " " & ActiveWorkbook.Names(i).Parent.Name
Next

The parent name it returns is always the name of the spreadsheet instead of the worksheet that it is part of. Am I doing something wrong? There are 6 worksheets in the workbook.

Where did you want me to use the .refersto property?

 

[SkipVought]

Function NameSheet(sValue As String) As String
    Dim sOut As String, sByte
    For i = 1 To Len(sValue)
        sByte = Mid(sValue, i, 1)
        Select Case sByte
        Case "'", "="
        Case "!"
            Exit For
        Case Else
            sOut = sOut & sByte
        End Select
    Next
    NameSheet = sOut
End Function

If you make the argument for this function the .RefersTo property of the Name, it will return the sheet name.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[mpezeur]

that works perfectly. Thank you!

 

[SkipVought]

Actually, I think this works a little better. You pass the function the Name Object...

Function NameSheet(nm As Name) As String
    Dim sOut As String, sByte
    For i = 1 To Len(nm.RefersTo)
        sByte = Mid(nm.RefersTo, i, 1)
        Select Case sByte
        Case "'", "="
        Case "!"
            Exit For
        Case Else
            sOut = sOut & sByte
        End Select
    Next
    NameSheet = sOut
End Function

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com