SOLVED

Hide/Unhide multiple Excel worksheets based on two cell values

%3CLINGO-SUB%20id%3D%22lingo-sub-2261237%22%20slang%3D%22en-US%22%3EHide%2FUnhide%20multiple%20Excel%20worksheets%20based%20on%20two%20cell%20values%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2261237%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20create%20a%20workbook%20which%20hides%20inactive%20sheets%20based%20on%20two%20cell%20values%20on%20the%20front%20sheet.%3C%2FP%3E%3CP%3EI%20tried%20using%20the%20Select%20case%20which%20hides%20worksheets%20based%20on%20one%20cell%20value%20but%20can't%20figure%20out%20to%20do%20the%20same%20based%20on%20to%20cells%20values.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFor%20Example%3A%3C%2FP%3E%3CP%3ECell%201%20Values%3A%3C%2FP%3E%3COL%3E%3CLI%3EA%3C%2FLI%3E%3CLI%3EB%3C%2FLI%3E%3C%2FOL%3E%3CP%3ECell%202%20Values%3A%3C%2FP%3E%3COL%3E%3CLI%3EC%3C%2FLI%3E%3CLI%3ED%3C%2FLI%3E%3CLI%3EE%3C%2FLI%3E%3C%2FOL%3E%3CP%3EWorksheets%20names%3A%3C%2FP%3E%3COL%3E%3CLI%3EX1%3C%2FLI%3E%3CLI%3EX2%3C%2FLI%3E%3CLI%3EX3%3C%2FLI%3E%3C%2FOL%3E%3CUL%3E%3CLI%3EShow%20Worksheet%20%22X1%22%20if%20Cell1%3DA%20and%20Cell2%3DC%20--%26gt%3B%20Hide%20Worksheet%26nbsp%3B%20X2%20and%20X3%3B%3C%2FLI%3E%3CLI%3EShow%20Worksheet%20%22X2%22%20if%20Cell1%3DB%20and%20Cell2%3DC%3B%3C%2FLI%3E%3CLI%3EShow%20Worksheet%20%22X3%22%20if%20Cell1%3DA%20and%20Cell2%3DD%3B%3C%2FLI%3E%3CLI%3EAnd%20so%20on..%3C%2FLI%3E%3C%2FUL%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThank%20you%20in%20advance%20for%20your%20help.%3C%2FP%3E%3CP%3ECheers%2C%3C%2FP%3E%3CP%3ERedi%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2261237%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
New Contributor

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

 

2 Replies
best response confirmed by RediM (New Contributor)
Solution

@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