Programmatically generate self referencing hyperlinks to run macros

Question

I am trying to run macro via a hyperlink on a generated worksheet.

Typically, this is done as shown here. This method requires the user to manually reference the cell they want to run the hyperlink through. I am able to get this method to work when testing however I can not do that on my generated worksheet. I have the code injection to the generated worksheet already written and tested (credit)(credit):

Sub CreateEventProcedure()
Dim VBProj As VBIDE.VBProject
Dim VBComp As VBIDE.VBComponent
Dim CodeMod As VBIDE.CodeModule
Dim LineNum As Long
Const DQUOTE = """" ' one " character

Set VBProj = ActiveWorkbook.VBProject
Set VBComp = VBProj.VBComponents(Worksheets("##WORKSHEET NAME##").CodeName)
Set CodeMod = VBComp.CodeModule
With CodeMod
    LineNum = .CreateEventProc("FollowHyperlink", "Worksheet")
    LineNum = LineNum + 1
    .InsertLines LineNum, "    ###INJECTED CODE GOES HERE##"
End With
End Sub

I have attempted to generate the hyperlink with no luck:

with ws
    .Hyperlinks.Add _
        Anchor:=.Range(.Cells(1, loc), .Cells(1, loc)), _
        Address:="'", _
        SubAddress:="'" & ws.Name & "'!" & .Range(.Cells(1, loc), .Cells(1, loc)).Address
end with

Of note, the final locations shown when hovering over the text are the exact same.

Any idea how to replicate the manual method of hyperlink generation to macros?

Gary's Student · Accepted Answer · 2019-10-25T20:00:09.543

1

If you want to insert hyperlinks into a set of cells that just jump to themselves, then Select them and run:

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

EDIT#1:

Based on Tim Williams' comment, here is an update:

Sub HyperAdder2()
   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

edited Oct 25 '19 at 20:00

answered Oct 25 '19 at 19:07

Gary's Student

95,722
10
59
99

Works as intended. I am getting a "reference is not valid." error when entering the hyperlink. Its manageable but do you know any way to get rid of this? – onyex Oct 25 '19 at 19:47
1

If the sheet name in SubAddress has spaces it needs to be enclosed by single quotes. – Tim Williams Oct 25 '19 at 19:53
Should've seen that. Thanks @TimWilliams and Gary – onyex Oct 25 '19 at 20:08

Programmatically generate self referencing hyperlinks to run macros

1 Answers1