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]

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 = "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
    Sheets("Other").Visible = False
    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
    If Target.Value <> "Select Entity" Then Sheets("Other").Visible = True
    Application.ScreenUpdating = True
End If
End Sub

 

[justanotheruser]

That unfortunately didn't work, so I tried:

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
    Sheets("Other").Visible = False
    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
    If Target.Value <> "Select Entity" Then Sheets("Other").Visible = [COLOR="Red"]False[/COLOR]
    Application.ScreenUpdating = True
End If
End Sub

But this means that Other doesn't work whatever I select now - should the <> be = instead?

 

[justanotheruser]

I tried to change the <> for =, but the other sheet is still hidden for both the Select Entity option and any other options.

 

[justanotheruser]

Okay, so I had another stab at the code and I managed to get it to work, I think the code is below if you could check it for me:

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" Then Sheets(i).Visible = False
        Next i
        Exit Sub
    Sheets("Other").Visible = False
    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 And .Range("D" & i).Value <> "" Then Sheets(.Range("D" & i).Value).Visible = True
        Next i
    End With
    If Target.Value <> "Select Entity" Then Sheets("Other").Visible = True
    Application.ScreenUpdating = True
End If
End Sub

Because this will be used by multiple end users, I would like to be able to protect the entire workbook so that they can't unhide/hide any sheets that haven't been defined in the mapping table. Therefore, is it possible to add to the code when a value is chosen drop the drop down list on the Title Page (cell E4), to unprotect the work book, show the relevant sheets, and reprotect the workbook? I don't know if that could actually be programmed into the the VBA code, if it could that would be awesome!

 

[justanotheruser]

I recorded a macro to see what the code is, and it's relatively simple so I inserted it as below:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, i As Long
[COLOR="red"]ActiveWorkbook.Unprotect
[/COLOR]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" Then Sheets(i).Visible = False
        Next i
        Exit Sub
    Sheets("Other").Visible = False
    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 And .Range("D" & i).Value <> "" Then Sheets(.Range("D" & i).Value).Visible = True
        Next i
    End With
    If Target.Value <> "Select Entity" Then Sheets("Other").Visible = True
    Application.ScreenUpdating = True
End If
[COLOR="Red"]ActiveWorkbook.Protect Structure:=True, Windows:=False
[/COLOR]End Sub

I can select any of the things in the drop down list and it will do its magic, but if I return to "Select Entity" - the first option in the drop down list the sheet is unprotected for some reason - probably because it is the first value of the cell. Any suggestions?