How to Hide multiple worksheet in excel using Multiple Drop down list using VBA

Question

I am creating a worksheet that will print different BOM's depending on what is selected. I have every BOM listed in the workbook under different Worksheets. The Main Worksheet will have Different Drop-down List to be selected as needed to created the necessary BOM.

What i am wanting to do is hide the worksheets that do not apply to the selected item in the drop down list's.

This is what i have right now

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = Me.Range("Media_System").Address Then
    If Target.Value = "Shop Vac" Then
        Sheets("Shop Vac Media Port Assembly").Visible = True
        Sheets("Shop Vac Assembly").Visible = True
        Sheets("Shop Vac Piping").Visible = True
    Else
        Sheets("Shop Vac Media Port Assembly").Visible = False
        Sheets("Shop Vac Assembly").Visible = False
        Sheets("Shop Vac Piping").Visible = False
    End If
End If
End Sub

This will only work for one drop down list, i currently have (7) drop down lists with different options in each. How do i make this work for each.

David Zemens David Zemens · Accepted Answer · 2015-02-19T15:17:11

You simply need to add more logic based on the Target cell address. The following snippet will handle your range "Media_System" and another range. To add more ranges corresponding with more dropdowns, simply copy & modify the If Target.Address... block and repeat as needed.

If Target.Address = Me.Range("Media_System").Address Then
    If Target.Value = "Shop Vac" Then
        Sheets("Shop Vac Media Port Assembly").Visible = True
        Sheets("Shop Vac Assembly").Visible = True
        Sheets("Shop Vac Piping").Visible = True
    Else
        Sheets("Shop Vac Media Port Assembly").Visible = False
        Sheets("Shop Vac Assembly").Visible = False
        Sheets("Shop Vac Piping").Visible = False
    End If
End If
'MODIFY & REPEAT AS NEEDED
If Target.Address = Me.Range("SOME_OTHER_RANGE").Address Then
    If Target.Value = "Some other thing" Then
        Sheets("foo").Visible = True
        Sheets("bar").Visible = True
        Sheets("boo").Visible = True
    Else
        Sheets("foo").Visible = False
        Sheets("bar").Visible = False
        Sheets("boo").Visible = False
    End If
End If

How to Hide multiple worksheet in excel using Multiple Drop down list using VBA

1 Answers