1

I have already created a macro that allows me to double click a hyperlink and it displays information from another sheet based on the cell that I have clicked on.

Now I would like to automate the hyperlinking process, what I need is a macro that will take a list of names and hyperlink all of those cells to themselves. so far I have a macro that hyperlinks the activated cell but returns a value of 0 but is hyperlinked ( first set of code)

I will include the macro that I recorded from manually adding the macro as well

Sub HyperLinkME()

frmla = "=HYPERLINK(" + Chr(34) + "#'" + ActiveSheet.name + "'!" +ActiveCell.Address + Chr(34) + "," + ActiveCell.Address + ")"
ActiveCell.Formula = frmla

End Sub

The macro that I recorded is as follows:

Sub ManualHyperlink()
'
' ManualHyperlink Macro
'
ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    "Schedule!A3", TextToDisplay:="dale"
End Sub

Thank you in advance for any and all help!

YowE3K
  • 23,852
  • 7
  • 26
  • 40
Sam
  • 27
  • 7

1 Answers1

1

Select some cells and run this:

Sub HyperAdder()
   For Each r In Selection
      ActiveSheet.Hyperlinks.Add Anchor:=r, Address:="", SubAddress:=r.Parent.Name & "!" & r.Address(0, 0), TextToDisplay:="myself"
   Next r
End Sub

to insert hyperlinks in the cells to jump to themselves.

To preserve the cell's contents, use:

Sub HyperAdder()
    Dim r As Range, s As String
    For Each r In Selection
        If Len(r.Text) = 0 Then
            s = "X"
        Else
            s = r.Text
        End If
        ActiveSheet.Hyperlinks.Add Anchor:=r, Address:="", SubAddress:=r.Parent.Name & "!" & r.Address(0, 0), TextToDisplay:=s
    Next r
End Sub
Gary's Student
  • 95,722
  • 10
  • 59
  • 99
  • that's a good start, all the cells I selected hyperlinked but they all say myself now. I need the display name to remain the same. Is that possible? – Sam May 26 '17 at 18:29
  • I got it to work with y= activecell.value and then putting that in the command. but it makes all of the cell values the name of the first one in the range instead of each individually, that might be a new question. Your code pretty much got me there though thank you so much – Sam May 26 '17 at 18:36
  • @Sam See my **EDIT** – Gary's Student May 26 '17 at 19:04