CODEWITHSUNDEEP
excelhyperlinkvba
James Watts
James Watts

Reputation: 41

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

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.

Upvotes: 0

Views: 1909

Answers (1)

Scott Holtzman
Scott Holtzman

Reputation: 27239

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

Upvotes: 1

Related Questions