VBA Code - Multiple Hyperlinks to Hidden Sheets and External Items (Error 5)

[ias2211]

Hi,

I have a spreadsheet that has hyperlinks in column A and column H.

The hyperlinks in column A directs you to a website outside of the workbook. But, the columns in H direct to hidden sheets within the workbook. In addition, each of the hidden sheets are coded with the following script so that when you leave the tab it disappears.

Private Sub Worksheet_Deactivate()
Me.Visible = xlSheetHidden
End Sub

I used the script below for the hyperlinks in column H. However, when you click the hyperlinks in column A, you receive a run time error '5' (invalid procedure). This obviously is because the hyperlinks in column A are not to hidden sheets (like column H).

So, my question is,

how do I change the script below to only apply/run on the hyperlinks in column H?


Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Dim strAddress As String

strAddress = Application.WorksheetFunction.Substitute(Target.SubAddress, "'", vbNullString)

ThisWorkbook.Worksheets(VBA.Left$(strAddress, VBA.InStr(1, strAddress, "!") - 1)).Visible = xlSheetVisible

Application.EnableEvents = False

Target.Follow

FixThings:

Application.EnableEvents = True
End Sub

 

[Micron]

how do I change the script below to only apply/run on the hyperlinks in column H?

Possibly test as

Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim strAddress As String

If Not ActiveCell.Column = 8 Then Exit Sub

Not sure how reliable that will be.
Please post code between code tags (vba button on posting toolbar) to maintain indentation and readability.

 

[ias2211]

Possibly test as

Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim strAddress As String

If Not ActiveCell.Column = 8 Then Exit Sub

Not sure how reliable that will be.
Please post code between code tags (vba button on posting toolbar) to maintain indentation and readability.

Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Dim strAddress As String 

worked. Thank you!!!

 

[Micron]

Glad to help. Can you mark this one as solved to save other responders time? Thanks.