Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Make Cell Addresses dynamic with hyperlinks in VBA (Excel 2000>)

    I found this very handy macro on the net quite a while back, that lists all the formulas and their cell references on a new sheet. I would like to know if it is possible for the macro to convert the Cell References that get added to the Address Column into hyperlinks so that after the macro has run, the user can click on the cell address, and it takes the person to the cell containing the formula.
    <pre>Sub ListFormulas()
    Dim FormulaCells As Range, Cell As Range
    Dim FormulaSheet As Worksheet
    Dim Row As Integer

    ' Create a Range object for all formula cells
    On Error Resume Next
    Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)

    ' Exit if no formulas are found
    If FormulaCells Is Nothing Then
    MsgBox "No Formulas."
    Exit Sub
    End If

    ' Add a new worksheet
    Application.ScreenUpdating = False
    Set FormulaSheet = ActiveWorkbook.Worksheets.Add
    FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name

    ' Set up the column headings
    With FormulaSheet
    Range("A1") = "Address"
    Range("B1") = "Formula"
    Range("C1") = "Value"
    Range("A1:C1").Font.Bold = True
    End With

    ' Process each formula
    Row = 2
    For Each Cell In FormulaCells
    Application.StatusBar = Format((Row - 1) / FormulaCells.Count, "0%")
    With FormulaSheet
    Cells(Row, 1) = Cell.Address _
    (RowAbsolute:=False, ColumnAbsolute:=False)
    Cells(Row, 2) = " " & Cell.Formula
    Cells(Row, 3) = Cell.Value
    Row = Row + 1
    End With
    Next Cell

    ' Adjust column widths
    FormulaSheet.Columns("A:C").AutoFit
    Application.StatusBar = False
    End Sub
    </pre>

    Regards,
    Rudi

  2. #2
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Make Cell Addresses dynamic with hyperlinks in VBA (Excel 2000>)

    Yes - something like:
    <pre>Sub ListFormulas()

    Dim FormulaCells As Range, Cell As Range
    Dim strCurrentSheetName As String
    Dim FormulaSheet As Worksheet, shtCurrent As Worksheet

    Dim Row As Integer
    Set shtCurrent = ActiveSheet
    strCurrentSheetName = shtCurrent.Name

    ' Create a Range object for all formula cells
    On Error Resume Next
    Set FormulaCells = Range("A1").SpecialCells(xlFormulas, 23)

    ' Exit if no formulas are found
    If FormulaCells Is Nothing Then
    MsgBox "No Formulas."
    Exit Sub
    End If
    ' Add a new worksheet
    Application.ScreenUpdating = False
    Set FormulaSheet = ActiveWorkbook.Worksheets.Add
    FormulaSheet.Name = "Formulas in " & FormulaCells.Parent.Name
    ' Set up the column headings
    With FormulaSheet
    .Range("A1") = "Address"
    .Range("B1") = "Formula"
    .Range("C1") = "Value"
    .Range("A1:C1").Font.Bold = True
    End With
    ' Process each formula
    Row = 2
    For Each Cell In FormulaCells
    Application.StatusBar = Format((Row - 1) / FormulaCells.Count, "0%")
    With FormulaSheet
    .Hyperlinks.Add Anchor:=.Cells(Row, 1), Address:="", _
    SubAddress:="'" & strCurrentSheetName & "'!" & _
    Cell.Address(RowAbsolute:=False, ColumnAbsolute:=False)
    .Cells(Row, 2) = " " & Cell.Formula
    .Cells(Row, 3) = Cell.Value
    Row = Row + 1
    End With
    Next Cell
    ' Adjust column widths
    FormulaSheet.Columns("A:C").AutoFit
    Application.StatusBar = False
    End Sub
    </pre>

    should do it.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: Make Cell Addresses dynamic with hyperlinks in VBA (Excel 2000>)

    Replace the instruction
    <code>
    Cells(Row, 1) = Cell.Address _
    (RowAbsolute:=False, ColumnAbsolute:=False)
    </code>
    with
    <code>
    .Hyperlinks.Add Anchor:=.Cells(Row, 1) , Address:="", _
    SubAddress:=Cell.Address(RowAbsolute:=False, ColumnAbsolute:=False, External:=True), _
    TextToDisplay:=Cell.Address(RowAbsolute:=False, ColumnAbsolute:=False)</code>

  4. #4
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make Cell Addresses dynamic with hyperlinks in VBA (Excel 2000>)

    Perfect Rory. This is a nice improvement on the original. Now its easy to navigate to the appropriate formula reference. I see it was not too much of an effort. You just built a concatenated address into a hyperlink. I will need to study this up and attempt it on my own now so I can do this for other ideas I have too. I think hyperlinks are more useful than what people percieve.

    Cheers to the code!
    Regards,
    Rudi

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make Cell Addresses dynamic with hyperlinks in VBA (Excel 2000>)

    Got it Hans. I see your code puts just the Cell Address into the added sheet. This is also good for W?B's with a single sheet. Many thx for the extra code.
    Appreciate it!
    Regards,
    Rudi

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Make Cell Addresses dynamic with hyperlinks in VBA (Excel 2000>)

    FYI, I also added periods in front of the object references within the With FormulaSheet...End With blocks (e.g. I changed <code>Cells</code> to <code>.Cells</code>) so that they specifically refer to the formulasheet (otherwise there is no point having the With...End With blocks!)
    Regards,
    Rory

    Microsoft MVP - Excel

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make Cell Addresses dynamic with hyperlinks in VBA (Excel 2000>)

    I never even noticed that, and I have been using the code alot. The code did run without problem before, however you are quite correct. You need to link the property to the object using the "point" in a with structure. Thx again!
    Regards,
    Rudi

  8. #8
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Make Cell Addresses dynamic with hyperlinks in VBA (Excel 2000>)

    It would only cause a problem in that example if you somehow managed to change sheets while the code is executing, which is pretty unlikely, but it's good to get in the habit of qualifying objects in that sort of structure or you can end up with problems that seem hard to debug!
    Regards,
    Rory

    Microsoft MVP - Excel

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Make Cell Addresses dynamic with hyperlinks in VBA (Excel 2000>)

    <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15> Only Hans would be able to do that with his 6 fingered hands!!!
    <img src=/S/thumbup.gif border=0 alt=thumbup width=15 height=15>
    Regards,
    Rudi

Posting Permissions

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