How to assign macros to multiple sets of hyperlinks in VBA excel

Question

I am having problems when assigning macros to hyperlinks using vba in excel. I can get it to work using the code below, when i have four hyperlinks in cells B2:B5 that are linked to their own cell.

Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
For i = 1 To 5
    If Target.Range.Address = "$B$" & 1 + i Then
    MsgBox "test" & i & " works!"
    End If
Next
End Sub

The problem comes when I want to add more hyperlinks in a different column that are also assigned to macros in the same sheet. Does anybody know how to do this?

Thanks in advance, JW.

I am not sure I understand. If they are in different columns then how does the code enter the IF/EndIf? — Siddharth Rout
– Siddharth Rout, Commented Aug 10, 2012 at 11:17

Scott Holtzman · Accepted Answer · 2012-08-10 13:36:14Z

1

How about this?

Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Select Case Target.Range.Column

    Case Is = 2, 3, 4 'change to whichever columns you need to reference

        For i = 1 To 5

            If Target.Range.Address = Cells(i + 1, Target.Range.Column).Address Then

                MsgBox "test " & i & " works!"

            End If

        Next

End Select


End Sub

answered Aug 10, 2012 at 13:36

Scott Holtzman

27.3k5 gold badges42 silver badges76 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

How to assign macros to multiple sets of hyperlinks in VBA excel

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related