VBA to hyperlink to hidden sheet which also has a hyperlink to another hidden sheet.

[GedSalter]

I have a workbook which has 100's of hidden sheets. I need to be able to hyperlink to a hidden sheet which may also contain a hyperlink to another hidden sheet. I have trie to use the following VBA code but it doesnt work on the newly opened sheets

Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)

Application.ScreenUpdating = False
Dim strLinkSheet As String
If InStr(Target.Parent, "!") > 0 Then
strLinkSheet = Left(Target.Parent, InStr(1, Target.Parent, "!") - 1)
Else
strLinkSheet = Target.Parent
End If
Sheets(strLinkSheet).Visible = True
Sheets(strLinkSheet).Select
Application.ScreenUpdating = True
End Sub

Private Sub Worksheet_Activate()
On Error Resume Next
Sheets(ActiveCell.Value2).Visible = False
End Sub


Hopefully someone can help. I think the problem maybe on the highlighted line

 

[elynoy]

I Use this on thisworkbook:

Private Sub Workbook_Open()
'Step 1:  Declare your variables
    Dim ws As Worksheet


'Step 2: Start looping through all worksheets
    For Each ws In ThisWorkbook.Worksheets
 
'Step 3: Unhide All Worksheets
    ws.Visible = xlSheetVisible


'Step 5:  Loop to next worksheet
    Next ws


'Step 6:  Hide the Start Sheet
    Sheets("SHEET1").Visible = xlVeryHidden


End Sub

and then, on the sheet itself, lets say sheet1 i have this

Private Sub Worksheet_Deactivate()
ThisWorkbook.Sheets(SHEET1").Visible = False 'hide
End Sub

This makes the sheet visible but if you click on another sheet it will hide again.

Best regards,
eLy

 

[GedSalter]

I Use this on thisworkbook:

Thanks for that but it didn't open up anything. Should I be changing the "sheet 1" to the name of the sheets?

regards

Ged

I should also add that some sheets may have up to 100 hyperlinks on them.

 

[elynoy]

Yes. Change the sheet names to your needs. I have around 40 sheets too. Or instead of using hidden sheets tou can Just hide the nav bar.

 

[GedSalter]

Hiding the Nav Bar would be awesome. How can I do that?

sorry to be a pain I am a novice at this.


regards

Ged

 

[elynoy]

Like this: Set ir False to hide the tabs and True to show the tabs.
Use it on Workbook_Open event

ActiveWindow.DisplayWorkbookTabs = True

best regards,
eLy

 

[GedSalter]

awesome. thank you

 

[elynoy]

no problem. glad i could help.

eLy