Hide/Unhide sheets based on change a cell value

[hoomantt]

Hi Drear professors;
i have a question and hope to help me
i have a workbook with several worksheets like attached example
i want when open this workbook , all sheets except of "menu" be hidden
in page "Menu" cell "B2" i have a list of all pages except of page "menu"
i want when i change value in cell "b2" , all sheets be hidden except of that page that named in cell "b2"
i hope i could explain correctly my quest.
thanks a lot

 

[Chihiro]

You need VBA for this. To hide all but "menu" sheet. Run this code on ThisWorkbook module.

Private Sub Workbook_Open()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "menu" Then
    ws.Visible = xlSheetVeryHidden
    Else
    End If
Next
End Sub

Then this code in Sheet2(menu) module.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sName As String
Dim ws As Worksheet

If Not Intersect(Target, Range("B2")) Is Nothing Then
    sName = ThisWorkbook.Worksheets("menu").Cells(2, 2).Value
    ThisWorkbook.Worksheets(sName).Visible = xlSheetVisible
End If

For Each ws In ActiveWorkbook.Worksheets
    If Not ws.Name = "menu" And Not ws.Name = sName Then
        ws.Visible = xlSheetVeryHidden
    Else
    End If
Next
End Sub