Apr 08 2021 06:30 AM - edited Apr 08 2021 06:32 AM
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:
Cell 2 Values:
Worksheets names:
Thank you in advance for your help.
Cheers,
Redi
Apr 08 2021 07:32 AM - edited Apr 08 2021 07:33 AM
SolutionFor 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
Apr 08 2021 07:49 AM
Apr 08 2021 07:32 AM - edited Apr 08 2021 07:33 AM
SolutionFor 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