OK, I've got one stage further.
My code is as follows
Option Explicit
Private Sub Worksheet_Activate()
Me.Visible = xlSheetVisible
Me.Cells.ClearContents
Cells(1, 1) = "Hidden Sheets"
Dim lastrow As Integer
lastrow = 2
Dim sh As Worksheet
For Each sh In Worksheets
If sh.Visible = False Then
Cells(lastrow, 1) = sh.Name
Me.Hyperlinks.Add anchor:=Cells(lastrow, 1), Address:="", SubAddress:=sh.Name
lastrow = lastrow + 1
End If
Next sh
Me.Columns(1).EntireColumn.AutoFit
End Sub
Private Sub Worksheet_Deactivate()
Me.Visible = xlSheetHidden
End Sub
Private Sub Worksheet_FollowHyperlink(ByVal Target As Hyperlink)
Sheets(Target.SubAddress).Activate
End Sub
At this moment, it will come up with an error, due to the subaddress value of hyperlink.add not having an actual cell range, but once I click on the message box, it opens the hidden sheet.
If I put a cell range ("A1" for example - and obviously with the additional quotation marks etc) onto the subaddress it no longer activates the sheet using the followhyperlink event.
Bookmarks