Hyperlink to 'Very Hidden Sheets'

[BenGee]

Hi - I've tried searching other threads before posting to no avail..

The workbook I'm building contains performance data on individuals, who'll have access to this book & to their respective data. There are 11 worksheets in total and is consisting of 1 worksheet as the main page and 10 other worksheets that will hold 10 individuals performance data (1 worksheet = 1 person)

Each worksheet (apart from the 'main page'), I've placed as "Very Hidden" within the VBA. However, I want a hyperlink that will take you to the 'Very Hidden' page upon selecting a persons name within a data validation list on the 'main page'. I'm using the Hyperlink function which sits right underneath the data validation list.

On a side note (in case it changes anything), I'm also looking to restrict permissions so that only 'Steve' can view his worksheet.

Any help would be greatly appreciated!
Ben

 

[BenGee]

Forgot to mention - the hyperlink function only works when the worksheets are visible and stops working when hidden

 

[ask2tsp]

Hello BenGee, for starters I have a little bit of code here. It shall not be spot on what you are looking for. I figured that it will be faster to start with something, tell what is good or wrong and go from there.
Here comes the code

Option Explicit

Sub makeVisibleAndGoto()
   Dim sht         As Worksheet
   Dim masterSheet As Worksheet
   Dim targetSheet As Worksheet
   Dim destination As Range
   
   Set masterSheet = ThisWorkbook.Worksheets("Master")   'assumed name of master sheet
   Set targetSheet = ThisWorkbook.Worksheets(ActiveCell.Value)
   Set destination = targetSheet.Range("A1")
   
   For Each sht In ThisWorkbook.Worksheets
      If Not sht Is masterSheet Then
         If sht Is targetSheet Then
            sht.Visible = xlSheetVisible
         Else
            sht.Visible = xlSheetVeryHidden
         End If
      End If
   Next sht
   
   destination.Worksheet.Activate
   destination.Select
End Sub

 

[Akuini]

However, I want a hyperlink that will take you to the 'Very Hidden' page upon selecting a persons name within a data validation list on the 'main page'.

Forgot to mention - the hyperlink function only works when the worksheets are visible and stops working when hidden

Why not create a named range for the list in the 'very hidden sheet' and use that named range for data validation list. It still will work even the sheet is very hidden.

 

[BenGee]

Thank you ask2tsp and Akuini - I'll try both and see how I get on!