Hide all sheets except for one based on cell value

Question

I have a workbook with a control sheet (named "Control") and many, many other sheets. I only need two sheets visible at any time. The two to be visible are the control sheet and the second sheet to be named in the Control sheet in cell A1. All other sheets are to be very hidden.

Answer 1

Hi,

in a regular module:

1st vba macro, visible All sheets

2nd vba macro, visible only two sheets

Option Explicit

'Feb 09, 2016

Dim sh As Worksheet

Const sN$ = "Control"

Const sCel$ = "A1" '<< sheet name, in cell A1

Sub Visible_ALL()

For Each sh In Sheets

sh.visible = True

Next

Sheets(sN).Select

End Sub

Sub Visible_Two_Shts()

Sheets(sN).Select

For Each sh In ThisWorkbook.Sheets

If sh.Name = Sheets(sN).Range(sCel).Value Or sh.Name = sN Then

sh.visible = True

Else

sh.visible = xlSheetVeryHidden

End If

Next

End Sub

XXXXXXXXXXXX

instead of visible Two sheets, macro (in a regular module)

i would suggest a 'private sub change event'

Right click on sheet tab (Control sheet), select View Code and paste in the following:

Private Sub Worksheet_Change(ByVal Target As Range)

Const sN$ = "Control"

Const sCel$ = "A1"

If Not Intersect(Target, Range(sCel)) Is Nothing Then

For Each sh In ThisWorkbook.Sheets

If sh.Name = Sheets(sN).Range(sCel).Value Or sh.Name = sN Then

sh.visible = True

Else

sh.visible = xlSheetVeryHidden

End If

Next

End If

End Sub

Save the file > Close > reopen the file

Answer 2

Question.  Instead of Const sN$ = "Control", would Const sN$ = Sheet10 work?

Answer 3

Hi. 

I was actually going to trigger this at workbook open and have a formula place the Environ Username in A1. This way the person opening the workbook will only see their sheet (based on their Environ name in A1) and the control sheet. So, could you assist with making it a regular macro so, I can trigger it on workbook open?

Thanx

Answer 4

Hi JW,

double click on ThisWorkbook in vb editor

and paste in.......

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Const sN$ = "Control"

If Not Intersect(Target, Sheets(sN).Range("A1")) Is Nothing Then

For Each Sh In ThisWorkbook.Sheets

If Sh.Name = Sheets(sN).Range("A1").Value Or Sh.Name = sN Then

Sh.visible = True

Else

Sh.visible = xlSheetVeryHidden

End If

Next

End If

End Sub