Hide/Unhide multiple Excel worksheets based on two cell values

Hi, I am trying to create a workbook which hides inactive sheets based on two cell values on the front sheet. I tried using the Select case which hides worksheets based on one cell value but can...

excel

HansVogelaar

Apr 08, 2021

RediM

For example, in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B1:B2"), Target) Is Nothing Then
        Select Case Range("B1").Value & Range("B2").Value
            Case "AC"
                Worksheets("X1").Visible = xlSheetVisible
                Worksheets("X2").Visible = xlSheetHidden
                Worksheets("X3").Visible = xlSheetHidden
            Case "AD"
                Worksheets("X1").Visible = xlSheetHidden
                Worksheets("X2").Visible = xlSheetHidden
                Worksheets("X3").Visible = xlSheetVisible
            Case "AE"
                ' add code here
            Case "BC"
                Worksheets("X1").Visible = xlSheetHidden
                Worksheets("X2").Visible = xlSheetVisible
                Worksheets("X3").Visible = xlSheetHidden
            Case "BD"
                ' add code here
            Case "BE"
                ' add code here
        End Select
    End If
End Sub

HansVogelaar

MVP

Apr 08, 2021

RediM

For example, in the worksheet module:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("B1:B2"), Target) Is Nothing Then
        Select Case Range("B1").Value & Range("B2").Value
            Case "AC"
                Worksheets("X1").Visible = xlSheetVisible
                Worksheets("X2").Visible = xlSheetHidden
                Worksheets("X3").Visible = xlSheetHidden
            Case "AD"
                Worksheets("X1").Visible = xlSheetHidden
                Worksheets("X2").Visible = xlSheetHidden
                Worksheets("X3").Visible = xlSheetVisible
            Case "AE"
                ' add code here
            Case "BC"
                Worksheets("X1").Visible = xlSheetHidden
                Worksheets("X2").Visible = xlSheetVisible
                Worksheets("X3").Visible = xlSheetHidden
            Case "BD"
                ' add code here
            Case "BE"
                ' add code here
        End Select
    End If
End Sub

RediM
Copper Contributor
Apr 08, 2021
It works, thanks!

Forum Discussion

Hide/Unhide multiple Excel worksheets based on two cell values

Resources