VBA to link hyperlink to hiddden worksheet - help with VBA code needed

[CristinaMR]

Hi all,

I'm trying to do the following, but am an absolut newbie to VBA, so this is having me terribly confused

I have created hyperlinks in my main excel sheet, linking me to about 20 tabs in the same worksheet. As I will distribute the sheet within my company, I want to hide the tabs (that the hyperlinks link to) and only show the main sheet with the consolidated data. The aim is, that the users will click on the link, the tab containing the data will open and once they go back it closes again.

I have researched earlier posts and found the following VBA code that sounds promising and seems to achieve what I am trying to do. Now, as i know nothing of VBA (yet) i need some help customizing the formula to my spreadsheet.

Long story short; this my question: Which parts of the below code do I need to update with my "data"?

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Dim strSheet As String

If InStr(Target.Parent, "!") > 0 Then
strSheet = Left(Target.Parent, InStr(1, Target.Parent, "!") - 1)
Else
strSheet = Target.Parent
End If

Sheets(strSheet).Visible = True
Sheets(strSheet).Select

End Sub
Private Sub Worksheet_Activate()

HideSheets

End Sub

Public Sub HideSheets()
Dim ws As Excel.Worksheet

For Each ws In ThisWorkbook.Worksheets
ws.Visible = (ws.Name = Me.Name)
Next
Set ws = Nothing
End Sub

Private Sub Workbook_Open()

HideSheets

End Sub

Any help is much, much appreciated!
Thanks,
Cristina

[CristinaMR]

Hi all,

so, i know for sure that the hiding and unhiding part of the VBA code works perfectly well! Now the only issue left is, that the Hyperlinks don't work, and I am pretty sure this is due to the below part of the code not being right yet.

I think the section in red is the part that i need to modify. But i don't know how!!

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Dim strSheet As String

If InStr(Target.Parent, "!") > 0 Then
strSheet = Left(Target.Parent, InStr(1, Target.Parent, "!") - 1)
Else
strSheet = Target.Parent
End If

Can anyone give me any pointers?
Thanks a lot,
Cristina

[CristinaMR]

No ideas anyone?