Hyperlink to 'Very Hidden Sheets'

BenGee

Board Regular
Joined
Mar 5, 2016
Messages
168
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
 

Some videos you may like

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

BenGee

Board Regular
Joined
Mar 5, 2016
Messages
168
Forgot to mention - the hyperlink function only works when the worksheets are visible and stops working when hidden
 

ask2tsp

Well-known Member
Joined
Feb 18, 2015
Messages
506
Office Version
  1. 365
Platform
  1. Windows
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
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

Well-known Member
Joined
Feb 1, 2016
Messages
2,936
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Mar 5, 2016
Messages
168
Thank you ask2tsp and Akuini - I'll try both and see how I get on!
 

Watch MrExcel Video

Forum statistics

Threads
1,122,604
Messages
5,597,128
Members
414,126
Latest member
jellevansoelen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top