Forum Discussion

RediM's avatar
RediM
Copper Contributor
Apr 08, 2021
Solved

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't figure out to do the same based on to cells values.

 

For Example:

Cell 1 Values:

  1. A
  2. B

Cell 2 Values:

  1. C
  2. D
  3. E

Worksheets names:

  1. X1
  2. X2
  3. X3
  • Show Worksheet "X1" if Cell1=A and Cell2=C --> Hide Worksheet X2 and X3;
  • Show Worksheet "X2" if Cell1=B and Cell2=C --> Hide Worksheet X1 and X3;
  • Show Worksheet "X3" if Cell1=A and Cell2=D --> Hide Worksheet X2 and X1;
  • And so on..

 

Thank you in advance for your help.

Cheers,

Redi

 

excel
  • HansVogelaar's avatar
    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

     

2 Replies

  • HansVogelaar's avatar
    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

     

Resources