Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1161
  • Last Modified:

Excel VBA - Get the range name of the current selection

How can I get the named range of the selected cells?

Something like

nmTxtString = Selection.NamedRange


Thanks, --Andres
0
AndresHernando
Asked:
AndresHernando
  • 6
  • 5
  • 5
  • +2
3 Solutions
 
gowflowCommented:
This should give you the named ranges and the cell they refer to in a workbook

For Each nm In ActiveWorkbook.Names
        CelRef =nm.RefersToRange.Address
        NamedRange = nm.Name
Next nm

CelRef returns the Cell Refrence
NamedRange returns the Name of the range

gowflow
0
 
gowflowCommented:
Actually to answer precisely your question
-----------------------------
For Each nm In ActiveWorkbook.Names
       if ActiveCell = nm.RefersToRange.Address then
             nmTxtString = nm.Name
             Exit For
       End if
Next nm
---------------------------------
gowflow
0
 
TommySzalapskiCommented:
Active cell will only give you the active cell in the selection. If you want it to work for the whole selection you need to use Selection.Address instead of ActiveCell
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Patrick MatthewsCommented:
gowflow,

With respect, I do not see how even your revised code answers the question.

I would try something like this.  Be advised that it is untested.  And note that it checks for Names scoped for both workbook and worksheet.

It also checks to see if the current selection intersects with more than one named range.

Sub CheckForName()

    Dim nm As Name
    Dim Result As String
    Dim rng As Range
    Dim Hits As Long

    Set rng = Selection
    For Each nm In ThisWorkbook.Names
        If Not Intersect(rng, nm.RefersToRange) Is Nothing Then
            Result = vbCrLf & Result
            Hits = Hits + 1
        End If
    Next
    For Each nm In ActiveSheet.Names
        If Not Intersect(rng, nm.RefersToRange) Is Nothing Then
            Result = vbCrLf & Result
            Hits = Hits + 1
        End If
    Next

    If Hits > 0 Then
        MsgBox "Current selection intersects following named ranges:" & vbCrLf & Result
    Else
        MsgBox "Current selection intersects no named ranges"
    End If

End Sub

Open in new window


Patrick
0
 
TommySzalapskiCommented:
gowflow's (with my edit) will work if the selection exactly matches the named range.
matthewspatrick's will tell you all the named ranges that contain any of the cells in the selection.

Are either of those what you are looking for or do you want all the named ranges that contain the entire selection?
0
 
AndresHernandoAuthor Commented:
goflow, Tommy:
thanks for the help.  This code works fine on a single worksheet.  However, I have multiple worksheets with thousands of named ranges which makes the code bug out because the Selection.Address can be in a different worksheet.  
Is there a way to restrict the scope to the names for the current range's workbook ?
0
 
TommySzalapskiCommented:
Change ActiveWorkbook to ThisWorkbook
0
 
TommySzalapskiCommented:
Actually, check if

ThisWorkbook.Names("bob").RefersTo = "'" & Selection.Worksheet.Name & "'!" & Selection.Address
0
 
broro183Commented:
hi Andres,

I like the use of intersect in Patrick's answer, however, I'm a fan of flexibility & not "reinventing the wheel" so...

Here is a link to The JKP Name Manager, is a very useful and free excel addin, collaboratively created by Jan Karel Pieterse (JKP), Charles Williams, ( www.decisionmodels.com ) and Matthew Henson ( mhenson@mac.com ) and can be downloaded from: http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp?AllComments=True 

I use it in my work almost every day and it is regularly updated as bugs or optimisations are identified so I occasionally compare the latest Build number on the website (currently it is #630) with the version on my machine.

hth
Rob
0
 
TommySzalapskiCommented:
So the whole code would look like
For Each nm In ThisWorkbook.Names
       If "='" & Selection.Worksheet.Name & "'!" & Selection.Address = nm.RefersTo Then
             nmTxtString = nm.Name
             Exit For
       End If
Next nm

Open in new window

0
 
AndresHernandoAuthor Commented:
Still trying to make it work...  The last code gives blanks for names.

My last comment should have read "worksheet" not "workbook".  I need to restrict the scope to the worksheet.  The last line should have read:

"Is there a way to restrict the scope to the names for the current range's worksheet ? "

If I could just restrict the ".Names" scope to the worksheet, your code would work great.
0
 
gowflowCommented:
For Each nm In Activesheet.Names
        CelRef =nm.RefersToRange.Address
        NamedRange = nm.Name
Next nm

gowflow
0
 
broro183Commented:
hi,

I realise that you are probably asking these questions to learn, rather than just to receive a working answer & I like that :-)

So... this post is just an extra, in case other people see this thread while searching for an answer to their problems - here are some solutions to your questions using JKP's Name Manager. After installing JKP's Name Manager addin, you can make the popup visible using [ctrl + shift + n] then...

"How can I get the named range of the selected cells?"
Choose the option "refers to selected cells" from the "Name Type (s)" option box on the right side of the popup.

"Is there a way to restrict the scope to the names for the current range's workbook?"
I'm not sure if this is exactly what you mean, but does choosing the option "Global" from the "Name Scope" option box on the right side of the popup give you what you want?
Oopps!
Just read your last post where you change the previous question to "Is there a way to restrict the scope to the names for the current range's worksheet?"
To do this choose the option "Local to activesheet" from the "Name Scope" option box on the right side of the popup. Or you could bring in some of Patrick's code which does a check using:
    For Each nm In ActiveSheet.Names
        If Not Intersect(rng, nm.RefersToRange) Is Nothing Then

Open in new window

Edit: Gowflow has already put this forward :-)

btw,
To create a listing of Names in your workbook you can push the button near the top left of the popup which has a self-explanatory mouse-over tip. This list can be filtered if necessary, based on the options selected in the boxes at the right of the popup, or using the autofilter on the created worksheet.

hth
Rob
0
 
gowflowCommented:
Oops sorry for the last post
this code is tested

put the below code in a module and call it this way in the worksheet selection change event
-------------------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim nm
Application.EnableEvents = False
Range("A1") = NamedRange
Application.EnableEvents = True
End Sub

It will put in A1 the named range of any cell you clik on that has a named range.
gowflow
Function NamedRange() As String
Dim nm
For Each nm In ActiveWorkbook.Names
       If ActiveCell.Offset(0, 0).Address = nm.RefersToRange.Offset(0, 0).Address Then
             NamedRange = nm.Name
             Exit For
       End If
Next nm

End Function

Open in new window

0
 
AndresHernandoAuthor Commented:
boro183,
as coincidence would have it, I installed JKP Name Manager a few days ago but haven't used it because I can't find where to call it (!?).  It shows in the Add In Manager with a checkbox, so it's installed.  Is it hidden in some menu I'm not finding?  I assume it's in the VBA editor menus.

Thanks, --Andres
0
 
AndresHernandoAuthor Commented:
gowflow,

goflow1.xlsm
0
 
AndresHernandoAuthor Commented:
gowflow, same problem...  It will get the range name of the wrong sheet.   See your code, attached in the last entry.
0
 
broro183Commented:
hi Andres,

I don't think it's much of a coincidence - I didn't realise until after posting, but I'm pretty sure it was me who suggested it to you ;-)

Sorry for the delayed response I'm wandering to & from my computer at the moment.
If it is showing with a tick, then the default shortcut key combination to open the interface is [ctrl + shift + N]. Alternatively, I'm using excel 2007 and I can see the icon at the right-hand end of the Formula Ribbon.
Or, in Excel 97…2003: a menu item called “Name Manager” is added at the bottom of the Tools menu.
 This and more is explained in the "Name Manager Manual 42.doc" Word document which is part of the download.

hth
Rob
0
 
broro183Commented:
btw, the options are in the main excel screen (not in the VBE screen).

hth
Rob
0
 
Patrick MatthewsCommented:
OK, this corrects some errors from my initial post.


Sub CheckForName()

    Dim nm As Name
    Dim ws As Worksheet
    Dim Result As String
    Dim ar As Range
    Dim rng As Range
    Dim Hits As Long
    
    Set rng = Selection
    For Each ar In rng.Areas
        For Each nm In ActiveWorkbook.Names
            If ar.Parent Is nm.RefersToRange.Parent Then
                If Not Intersect(ar, nm.RefersToRange) Is Nothing Then
                    Result = Result & vbCrLf & nm.Name
                    Hits = Hits + 1
                End If
            End If
        Next
        For Each ws In ActiveWorkbook.Worksheets
            For Each nm In ws.Names
                If ar.Parent Is nm.RefersToRange.Parent Then
                    If Not Intersect(ar, nm.RefersToRange) Is Nothing Then
                        Result = Result & vbCrLf & nm.Name
                        Hits = Hits + 1
                    End If
                End If
            Next
        Next
    Next
    
    If Hits > 0 Then
        MsgBox "Current selection intersects following named ranges:" & vbCrLf & Result
    Else
        MsgBox "Current selection intersects no named ranges"
    End If

End Sub

Open in new window

0
 
gowflowCommented:
Is this what your looking for ?
gowflow
goflow1.xlsm
0
 
gowflowCommented:
tks for the grade
gowflow
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 6
  • 5
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now