Show/Hide Specific (Changeable) Sheets based on Cell Value

[justanotheruser]

Hi guys,

I've been searching forums to no end to try to find a solution to this, but I've hit a road block.

I have a sheet called "Title" and cell E4 in this sheet is a drop down list which allows you to select certain codes, for example 210, 220 or 230.

Based on values in this cell, I would like to show certain worksheets, which are defined in another sheet, "Mapping", that contains the following values in Column C and D respectively.

Code Sheet Name Shown
210 PL_Workwear
210 BS_Workwear
220 PL_Cleanroom
220 BS_Cleanroom

I've managed to do this if I just enter the sheet names in the VBA code myself, but unfortunately it needs to be done so that a user who doesn't know how to use VBA can add more sheets and sheet names that will be shown by simply adding a line to the Mapping sheet, so they could add the line:

210 MM_Workwear

To the mapping sheet, and then if they select code 210 from the drop down box in Cell E4 in the Title sheet, then PL_Workwear, BS_Workwear and MM_Workwear will need to be shown.

My VBA isn't good enough to know how to do this - use dynamic ranges or VLOOKUPs with more than one value like on http://office.microsoft.com/en-us/excel-help/how-to-look-up-a-value-in-a-list-and-return-multiple-corresponding-values-HA001226038.aspx#_Toc273640147?

Any help or code you could provide would be very much appreciated!

 

[VoG]

Sorry, I omitted an End If

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, i As Long
If Target.Address(False, False) = "E4" Then
    If Target.Value = "" Then
        For i = 1 To Worksheets.Count
            If Sheets(i).Name <> "Title" And Sheets(i).Name <> "Mapping" Then Sheets(i).Visible = False
        Next i
        Exit Sub
    End If
    Application.ScreenUpdating = False
    For i = 1 To Worksheets.Count
        If Sheets(i).Name <> "Title" And Sheets(i).Name <> "Mapping" Then Sheets(i).Visible = False
    Next i
    With Sheets("Mapping")
        LR = .Range("C" & Rows.Count).End(xlUp).Row
        For i = 3 To LR
            If Target.Value = .Range("C" & i).Value Then Sheets(.Range("D" & i).Value).Visible = True
        Next i
    End With
    Application.ScreenUpdating = True
End If
End Sub

 

[justanotheruser]

No problem, in fact if anything I should thank you for making that mistake, now I know where the end if should have gone, learn a little every day!

 

[justanotheruser]

I tried to set it so that it allows another sheet, "Other" to be open no matter what you choose from the drop down list, see my code below, but it doesn't seem to allow the sheet "Other" - is there any alternative way to do this, as I have 20 sheets that should be open no matter what you choose from the drop down list, and then the other sheets are opened dependent on your drop down list choice.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, i As Long
If Target.Address(False, False) = "E4" Then
    If Target.Value = "" Then
        For i = 1 To Worksheets.Count
            If Sheets(i).Name <> "Title" And Sheets(i).Name <> "Mapping" And Sheets(i).Name <> "Other" Then Sheets(i).Visible = False
        Next i
        Exit Sub
    End If
    Application.ScreenUpdating = False
    For i = 1 To Worksheets.Count
        If Sheets(i).Name <> "Title" And Sheets(i).Name <> "Mapping" And Sheets(i).Name <> "Other" Then Sheets(i).Visible = False
    Next i
    With Sheets("Mapping")
        LR = .Range("C" & Rows.Count).End(xlUp).Row
        For i = 3 To LR
            If Target.Value = .Range("C" & i).Value Then Sheets(.Range("D" & i).Value).Visible = True
        Next i
    End With
    Application.ScreenUpdating = True
End If
End Sub

Thanks again.

 

[VoG]

Maybe like this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, i As Long
If Target.Address(False, False) = "E4" Then
    If Target.Value = "" Then
        For i = 1 To Worksheets.Count
            If Sheets(i).Name <> "Title" And Sheets(i).Name <> "Mapping" And Sheets(i).Name <> "Other" Then Sheets(i).Visible = False
        Next i
        Exit Sub
    End If
    Application.ScreenUpdating = False
    For i = 1 To Worksheets.Count
        If Sheets(i).Name <> "Title" And Sheets(i).Name <> "Mapping" And Sheets(i).Name <> "Other" Then Sheets(i).Visible = False
    Next i
    With Sheets("Mapping")
        LR = .Range("C" & Rows.Count).End(xlUp).Row
        For i = 3 To LR
            If Target.Value = .Range("C" & i).Value Then Sheets(.Range("D" & i).Value).Visible = True
        Next i
    End With
    Sheets("Other").Visible = True
    Application.ScreenUpdating = True
End If
End Sub

 

[justanotheruser]

That's a much better fix that mine, no need to enter the code twice! If on my table I was going to have something like this, where an entity does not need to open any particular new sheets (but would need the static pages open such as the Other page previously mentioned), is it possible to modify the code so that it doesn't do any particular action if the corresponding cell in Column D is blank - as in C11 and D11, without giving the Subscript out of range error?

Excel Workbook
	C	D
1	Entity	Sheet Name
2	*	*
3	FA6_210	PL_Workwear
4	FA6_210	BS_Workwear
5	FA6_210	PL_Cleanroom
6	FA7_220	PL_Workwear
7	FA8S_220	BS_Workwear
8	FA89_230	PL_Cleanroom
9	KK_230	PL_Workwear
10	KK_230	BS_Workwear
11	WW_21	*
12	WW_11	*
13	AA_11	*
Mapping

Thank you again - you deserve your MVP title!

 

[justanotheruser]

I think the basic concept would be something like if .Range("D" = "" Then Exit Sub but I don't think that's the right code for it, and where to put it.

 

[VoG]

Try

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, i As Long
If Target.Address(False, False) = "E4" Then
    If Target.Value = "" Then
        For i = 1 To Worksheets.Count
            If Sheets(i).Name <> "Title" And Sheets(i).Name <> "Mapping" And Sheets(i).Name <> "Other" Then Sheets(i).Visible = False
        Next i
        Exit Sub
    End If
    Application.ScreenUpdating = False
    For i = 1 To Worksheets.Count
        If Sheets(i).Name <> "Title" And Sheets(i).Name <> "Mapping" And Sheets(i).Name <> "Other" Then Sheets(i).Visible = False
    Next i
    With Sheets("Mapping")
        LR = .Range("C" & Rows.Count).End(xlUp).Row
        For i = 3 To LR
            If Target.Value = .Range("C" & i).Value And .Range("D" & i).Value <> "" Then Sheets(.Range("D" & i).Value).Visible = True
        Next i
    End With
    Sheets("Other").Visible = True
    Application.ScreenUpdating = True
End If
End Sub

 

[justanotheruser]

That works, thanks! I've created a dynamic range for my drop down list using the following:

Excel Workbook
	A	B	C	D
1	*	*	Entity	Additional Sheets to open
2	*	*	*	*
3	1	FA6_210	FA6_210	PL_Workwear
4	1	FA7_220	FA6_210	BS_Workwear
5	1	FA8S_220	FA6_210	PL_Cleanroom
6	2	FA89_230	FA7_220	PL_Workwear
7	3	KK_230	FA8S_220	BS_Workwear
8	4	AA_11	FA89_230	PL_Cleanroom
9	5	a1	KK_230	PL_Workwear
10	5	*	KK_230	BS_Workwear
11	6	*	AA_11	*
12	7	*	a1	*
Mapping

If we look at Row B, where I have defined a name, called ENTA with the following: =OFFSET(Mapping!$B$3,0,0,COUNTA(Mapping!$B:$B),1) - in cell B10, even though the cell value is blank, because there is a formula in it, the cell is chosen as part of the dynamic range - is there a way to adjust the offset formula to fix this? The cells in Column B are formatted as [=0]"";General -- thanks again

 

[VoG]

Dynamic ranges are not my forte - I suggest that you start a new thread for this different question.

 

[justanotheruser]

Okay, thank you - I have created a new thread as you suggested. I've changed the code so that if the first value says "Select Entity" (instead of it being blank), then it will actually need to hide all sheets other than the Title sheet, and then when you select one of the other values from the drop down list, it will open corresponding sheets. I tried just adding the Sheets("Other")... line code, but it didn't work - can it be done to hide all other sheets or alternatively if this is not possible hard code which other sheets (the static sheets that everyone can view no matter what they select from the drop down), including "Other"?

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, i As Long
If Target.Address(False, False) = "E4" Then
    If Target.Value = "Select Entity" Then
        For i = 1 To Worksheets.Count
            If Sheets(i).Name <> "Title" And Sheets(i).Name <> "Mapping" And Sheets(i).Name <> "Other" Then Sheets(i).Visible = False
        Next i
        Exit Sub
    [COLOR="Red"]Sheets("Other").Visible = False[/COLOR]
    End If
    Application.ScreenUpdating = False
    For i = 1 To Worksheets.Count
        If Sheets(i).Name <> "Title" And Sheets(i).Name <> "Mapping" And Sheets(i).Name <> "Other" Then Sheets(i).Visible = False
    Next i
    With Sheets("Mapping")
        LR = .Range("C" & Rows.Count).End(xlUp).Row
        For i = 3 To LR
            If Target.Value = .Range("C" & i).Value And .Range("D" & i).Value <> "" Then Sheets(.Range("D" & i).Value).Visible = True
        Next i
    End With
    Sheets("Other").Visible = True
    Application.ScreenUpdating = True
End If
End Sub

I highlighted my change in red.