3 thoughts on “Name Manager Updated”

1. How about a “Go to referred cell”? This differs from Edit/Goto… in that if the sheet that the reference is on is hidden, it makes it visible. Also, flash the cell in red for a half a second. If the named range is larger than a single cell, flash the upper-left cell (so you don’t have to keep track of and replace the interior fill colors of a large range). Also, it should work for dynamic ranges, like “=OFFSET(Filters!$E$2,1,0,nCorpFund,1)”, which Edit/Goto doesn’t recognize.

   I use this quite often and have a small utility I wrote to do this. Here’s the code, which uses the range name selected in a listbox:

   Sub DoGoToName()
   Dim sX As String

       On Error GoTo Err_Me
       sX = lstNames.List(lstNames.ListIndex, 0)
       With Range(sX)
           Sheets(.Parent.Name).Visible = True
           Sheets(.Parent.Name).Activate
           .Select
       End With
       FlashCell
      
   Exit_Me:
       Exit Sub
   Err_Me:
       Resume Exit_Me
   End Sub

   Sub FlashCell()
   Dim t As Single, i As Integer, SaveColor As Integer
      
       On Error GoTo Err_Me
       t = Timer
       With Selection.Cells(1, 1)
           SaveColor = .Interior.ColorIndex
           For i = 1 To 6
               .Interior.ColorIndex = IIf(i Mod 2 = 1, 3, xlNone)
               While t + (i * 0.1) > Timer
                   DoEvents
               Wend
           Next i
           .Interior.ColorIndex = SaveColor
       End With
      
   Exit_Me:
       Exit Sub
   Err_Me:
       Resume Exit_Me
   End Sub

   Thanks,

   Dave

2. Most of this function is already in Name Manager.
   The GoTo function Name manager (double-click the name or click the GoTo Icon) already handles Dynamic Named Ranges and ranges that refer to other open workbooks. When you use the Name Manager GoTo function it selects all the cells that are defined in the range so that you can visibly see what cells are in the Named Range.
   If you want more emphasis you can use the Highlight and Clear Highlights buttons for either the selected Names or all Names.

3. Important message:
   For those of you who downloaded the 2007/2010 in the last couple of days, please visit my site again and download a fresh copy; the previous 2007 download did not have the Ribbon controls included!

---

Posting code? Use <pre> tags for VBA and <code> tags for inline.