Results 1 to 7 of 7
  1. #1
    New Lounger
    Join Date
    Jul 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Named Cells and Ranges (Excel XP)

    <img src=/S/hello.gif border=0 alt=hello width=25 height=29> I have used the Excel Utilities Add-In to print a list of all named cells and ranges in a notebook. The resulting list contains two fields - Names and Refers To. What I would like is macro solution (there are 200+ names) that would turn the Names into hyperlinks to the actual location represented by the name. Having just started looking into vba I don't really know where to begin. <img src=/S/scratch.gif border=0 alt=scratch width=25 height=29> Does anyone know a solution.

  2. #2
    2 Star Lounger
    Join Date
    Dec 2000
    Posts
    140
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Cells and Ranges (Excel XP)

    Sorry to question the question: Why not just display the auditing arrows (i.e. the Dependents) and then click on the blue arrow lines ?

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Named Cells and Ranges (Excel XP)

    I assume you mean the Excel Utilities by Rob Bovey. In the version I have, the Enumerate Names menu option creates a new workbook with the list of names. The names are in column B.

    Here is a macro that will work for book-level names. The name of the source workbook is not mentioned in the new workbook, so you need to supply it. In this macro this is done by a simple InputBox. The workbook with the list of names must be active when you run the macro.

    Sub CreateHyperLinks()
    Dim strWorksheet As String
    Dim rngNames As Range
    Dim rngCell As Range
    Set rngNames = ActiveSheet.Range("B2", Range("B2").End(xlDown))
    strWorksheet = InputBox("Name of workbook?")
    If strWorksheet = "" Then Exit Sub
    For Each rngCell In rngNames
    ActiveSheet.Hyperlinks.Add _
    Anchor:=rngCell, _
    Address:=strWorksheet, _
    SubAddress:=rngCell.Value
    Next rngCell
    End Sub

    Note: the underscores _ in the code are continuation characters. They indicate that the instruction is continued on the next line. There needs to be a space before them.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Cells and Ranges (Excel XP)

    In the little box to the left of your formula bar, there is a dropdown arrow. Click it and you'll get the names. Choose one to go there.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  5. #5
    New Lounger
    Join Date
    Jul 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Cells and Ranges (Excel XP)

    Thanks to everyone who replied. <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> . HansV thank you for supplying credit for the Add-In. That is exactly what I had in mind. <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22> Not to be a pain - but, How would you modify the macro under the following conditions:
    1 the name list was copied and pasted into the workbook that contains the named ranges.
    2. Was named "NameDatabase" which refers to [Formulas.xls]Index!$A$25:E272
    3. The columns were moved around to enable a vlookup function. Columns now = A- Name, B-Refers To,C-Scope,D-Visible,E-Length.
    4. And last but not least: the macro will be used exactly the same way where [workbook.xls]Index!$a$25:Ex (workbook and exact cell range are variables.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Named Cells and Ranges (Excel XP)

    If the sheet with the name list is the active sheet, use the following macro.

    Sub CreateHyperLinks()
    Dim rngNames As Range
    Dim rngCell As Range
    Set rngNames = ActiveSheet.Range("A25", Range("A25").End(xlDown))
    For Each rngCell In rngNames
    ActiveSheet.Hyperlinks.Add _
    Anchor:=rngCell, _
    Address:="", _
    SubAddress:=rngCell.Value
    Next rngCell
    End Sub

    If you want to be able to run it for arbitrary open workbooks (not necessarily the active one), use the following macro. Replace "workbookname.xls" by the actual name, or by a string variable containing the name of an open workbook.

    Sub CreateHyperLinks()
    Dim wbk As Workbook
    Dim wsh As Worksheet
    Dim rngNames As Range
    Dim rngCell As Range
    Set wbk = Workbooks("workbookname.xls")
    Set wsh = wbk.Worksheets("Index")
    Set rngNames = wsh.Range("A25", Range("A25").End(xlDown))
    For Each rngCell In rngNames
    wsh.Hyperlinks.Add _
    Anchor:=rngCell, _
    Address:="", _
    SubAddress:=rngCell.Value
    Next rngCell
    End Sub

    HTH,
    Hans

  7. #7
    New Lounger
    Join Date
    Jul 2002
    Posts
    9
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Named Cells and Ranges (Excel XP)

    Many thanks. <img src=/S/joy.gif border=0 alt=joy width=23 height=23>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •