Specifying Multiple Sheets/String Array?

Question

I and working with some code that aims to Hide/Unhide Sheets based on a selection from a dropdown menu. The setup is:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Target.Address = Range("SelectCP").Address Then
    Select Case Target.Value
      Case "All"
        ShowAllSheets
      Case ""
        'do nothing
      Case Else
        ShowSelSheets
        'do nothing
    End Select
  End If
End Sub

Where functions ShowAllSheets and ShowSelSheets are defined as:

Sub ShowAllSheets()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
  ws.Visible = xlSheetVisible
Next ws
End Sub


Sub ShowSelSheets()
Dim ws As Worksheet
Dim strType As String
strType = Worksheets("Counterparty Overview").Range("SelectCP").Value
For Each ws In ActiveWorkbook.Sheets
  If InStr(1, ws.Name, strType) > 0 Then
    ws.Visible = xlSheetVisible
  Else
    If ws.Name <> "Counterparty Overview" Then
      ws.Visible = xlSheetHidden
    End If
  End If
Next ws
End Sub

The trouble I'm having is with this bit:

Else
If ws.Name <> "Counterparty Overview" Then
  ws.Visible = xlSheetHidden

I want to specify two other sheets in addition to "Counterparty Overview" to always show. I've tried to initiate strType as a variable array, but get an error. Any Ideas?

Answer 1

Try this:

Dim SomeSheet as String
SomeSheet = "Other Sheet Name"

If ws.Name <> "Counterparty Overview" Then
  ws.Visible = xlSheetHidden
  Sheets(SomeSheet).Visible = xlVisible
  'or
  Sheets("Other Sheet Name").Visible = xlVisible

End If