Combo box - Select all worksheets except...

[SeverusGrape]

Hi, first time poster here and rookie Excel user.

I have a very simple bit of code to select which worksheet will be active from a combo box.
I want to exclude specific worksheets from that combo box.

The idea, basically, is to have a different worksheet for each week of the year. This is for rostering. But rather than having a long string of worksheets to select from on the tab section, I want to hide the weekly sheets and select the work week from the combo box instead. That way I can have the other few worksheets I'll be using on this workbook to select from on the tab section without all the date worksheets cluttering it up.

Any help is appreciated. Thanks in advance.

My code for the combo box, to select a worksheet is...

Private Sub cboStart_Change()

If cboStart <> "Choose Sheet" Then
Worksheets(cboStart.Value).Select
End If

cboStart.Value = "Choose Sheet"

End Sub

Private Sub Worksheet_Activate()

Dim sht As Worksheet
Me.cboStart.Clear
For Each sht In ThisWorkbook.Worksheets
Me.cboStart.AddItem sht.name

Next sht

End Sub

 

[Legacy 143009]

This example will exclude Sheet1 and Sheet2

Private Sub cboStart_Change()

  If cboStart <> "Choose Sheet" Then
    Worksheets(cboStart.Value).Select
  End If

  cboStart.Value = "Choose Sheet"

End Sub
Private Sub Worksheet_Activate()
  Dim excludedWorksheets() As String
  Dim sht As Worksheet
  Dim wsVar As Boolean
  excludedWorksheets = Split("Sheet1,Sheet2", ",")

  Me.cboStart.Clear
  For Each sht In ThisWorkbook.Worksheets
    wsVar = False
    For i = 0 To UBound(excludedWorksheets)
      If sht.Name = excludedWorksheets(i) Then
        wsVar = True
      End If
      If Not wsVar Then
        Me.cboStart.AddItem sht.name
      End If
    Next
  Next
End Sub

 

[HaHoBe]

Hi SeverusGrape,

if the sheets you want to display start with a constant like Week you could use something like

Private Sub cboStart_Change()

If cboStart <> "Choose Sheet" Then
  Worksheets(cboStart.Value).Select
End If

cboStart.Value = "Choose Sheet"

End Sub

Private Sub Worksheet_Activate()

Dim wks As Worksheet

Const cstrStartWks As String = "Week"   '<-- change to suit

Me.cboStart.Clear
For Each wks In ThisWorkbook.Worksheets
  If UCase(Left(wks.Name, Len(cstrStartWks))) = UCase(cstrStartWks) Then Me.cboStart.AddItem wks.Name
Next wks

End Sub

Ciao,
Holger