VBA macro to have multiple select dropdown and hide worksheets

[LPO]

Hi,

I'm looking to have a macro that will allow me to have multiple selections from a dropdown list and then filter out worksheets based on those selections. For example in my document, I would like to be able to select both "Manufacturing" and "Application" from the dropdown list in B3 and then have those worksheets appear. If there are no selections, no worksheets appear.

I am just starting to use/learn VBA so I've found online two macros - one that hides the sheets and one that allows multiple selection in the dropdown. But when I put them together, only one works. I'm not sure what I'm doing wrong. Any help is appreciated and let me know if you need more information! I'm an excel newbie so I'm sorry if I sound silly.

Code for hiding sheets:
>>> use code - tags <<<

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim Ws As Worksheet
    If Target.Cells.CountLarge > 1 Then Exit Sub
    If Not Intersect(Target, Range("B3")) Is Nothing Then
        For Each Ws In ThisWorkbook.Worksheets
            If Not Ws Is Sheet1 Then
                If Target.Value = Ws.Name Then
                    Ws.Visible = xlSheetVisible
                    Else
                    Ws.Visible = xlSheetVeryHidden
                    End If
                End If
            Next Ws
        End If
End Sub

Code for multiple selection:

Private Sub Worksheet_Change(ByVal Target As Range)
'Code by Sumit Bansal from https://trumpexcel.com
' To allow multiple selections in a Drop Down List in Excel (without repetition)
Dim Oldvalue As String
Dim Newvalue As String
Application.EnableEvents = True
On Error GoTo Exitsub
If Target.Address = "$B$3" Then
  If Target.SpecialCells(xlCellTypeAllValidation) Is Nothing Then
    GoTo Exitsub
  Else: If Target.Value = "" Then GoTo Exitsub Else
    Application.EnableEvents = False
    Newvalue = Target.Value
    Application.Undo
    Oldvalue = Target.Value
      If Oldvalue = "" Then
        Target.Value = Newvalue
      Else
        If InStr(1, Oldvalue, Newvalue) = 0 Then
            Target.Value = Oldvalue & ", " & Newvalue
      Else:
        Target.Value = Oldvalue
      End If
    End If
  End If
End If
Application.EnableEvents = True
Exitsub:
Application.EnableEvents = True
End Sub

 

[vletm]

LPO
How did You put those together?
... Your sample file cannot have any codes.

Added for LPO
a) Those codes have to be in correct place.
b) There is a challenge, if same code is twice.

 

[LPO]

LPO
How did You put those together?
... Your sample file cannot have any codes.

Hi! Sorry about that, I removed the code from the file. I had them like this:

Moderator note:
Did You check Your original writing?
Did You notice that something had changed?

 

[Marc L]

I would like to be able to select both "Manufacturing" and "Application" from the dropdown list in B3

Hi, so obviously use B3 & B4 …​

​

 

[LPO]

Hi, so obviously use B3 & B4 …​

​

Hi. I tried doing a second dropdown in B4 but then when selecting in those cells, the worksheets didn't hide.

 

[vletm]

LPO
If You did as You write above, it's normal.
The Excel has rules - how do it work.
... eg it won't like that there are more than once Private Sub Worksheet_Change(ByVal Target As Range)
Without a clear sample Excel-file, other could ... guess.

 

[Marc L]

As guessing can't be coding, just wasting time, no worth to give it a try …​

​