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
    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
End If
End Sub

 

[justanotheruser]

Hi,

It opens the sheets if they are hidden but then gives the subscript out of range error, on the code

Sheets(.Range("D" & i).Value).Visible = True

:S

 

[justanotheruser]

Oops, scratch that, one of the sheets didn't exist - that's why I had that problem!

One more question, say in my table on the Mapping sheet, if you look the codes for 210 and 220:

Excel Workbook
	C	D
2	Entity	Sheet Name
3	210	PL_Workwear
4	210	BS_Workwear
5	210	PL_Cleanroom
6	220	PL_Workwear
7	220	BS_Workwear
Mapping

If you select "220" you should only be able to see the ones above that have been defined, i.e. you should not be able to see the sheet PL_Cleanroom. However, if I select 210 first as my code and then select 220 I can still see PL_Cleanroom unhid. Is there a way to do it so that it only shows it if it can find it on the mapping sheet above?

Thanks again.

 

[VoG]

Maybe this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim LR As Long, i As Long
If Target.Address(False, False) = "E4" Then
    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]

Sorry for the late reply, wow you're an Excel Genius is all I can say! I want to start off with the drop down list on the Title page being blank, so on the Mapping sheet I added the first row as essentially blank:

Excel Workbook
	C	D
2	Entity	Sheet Name
3
4	210	PL_Workwear
5	210	BS_Workwear
6	210	PL_Cleanroom
7	220	PL_Workwear
8	220	BS_Workwear
9	230	PL_Cleanroom
10	230	PL_Workwear
11	230	BS_Workwear
Mapping

The only problem this causes is that if I change from the blank default to any code and then try to change back, I get the Subscript out of range error (9) because no data exists in the cell for the sheet to show. Would it be possible to stop this error from occurring by adjusting the code? Thank you in advance!

 

[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 Exit Sub
    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]

Hi,

That stops the error code, but if I select the blank value, could you add something that hides the other sheets (PL_Workwear, BS_Workwear etc?)

Thanks!

 

[VoG]

Maybe or you can adapt to suit

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
    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]

I get "Compile Error: Block if without end if". I tried adding a second End If before the End Sub, but I'm new to VBA and that didn't work

 

[justanotheruser]

If it helps, the first line of code:

Private Sub Worksheet_Change(ByVal Target As Range)

Is what is highlighted in the debugger.