Combobox listing Sheet Names

[mar050703]

Please can someone help?

I have the following code in a workbook of about 35 sheets:

Sub Workbook_Open()
      


 Dim s As Worksheet
 Dim varSelect
 Dim i As Integer
   i = 0
   Sheet1.Activate
   Set varSelect = Sheet26.ComboBox1
   With varSelect
     For Each s In Worksheets
       .AddItem s.Name, i
       i = i + 1
     Next s
   End With

ActiveWorkbook.Sheets("Print").Activate
Range("A1").Select


End Sub

However I generally would only need access to half these, is there a way to exclude certain sheets (in my case Sheets 2-13) named April, May, etc to March)

Thanks in advance for your help.

 

[Andrzejek]

You may do this:

...
   With varSelect
     For Each s In Worksheets
       Select Case s.Name
           Case "April", "May", ...,  "March"[green]
               'By-Pass them[/green]
           Case Else
             .AddItem s.Name, I
      End Select
       i = i + 1
     Next s
   End With
...

Not very 'elegant' way, but it should work.

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[mar050703]

Thanks Andy

Almost there, but sadly when I open the workbook, using that code I get an error message simply saying 'Invalid argument' and the option to debug, when I hit debug, the code:

  .AddItem s.Name, I

is marked as yellow

the code I have used is:

Sub Workbook_Open()
      
 Dim s As Worksheet
 Dim varSelect
 Dim i As Integer
   i = 0
   Sheet1.Activate
   Set varSelect = Sheet26.ComboBox1
  
    With varSelect
    For Each s In Worksheets
     Select Case s.Name
        Case "April", "May", "June" 'etc 
        Case Else
        .AddItem s.Name, i
     End Select
     i = i + 1
     Next s
    End With

ActiveWorkbook.Sheets("Print").Activate
Range("A1").Select

End Sub

Am I missing something?

 

[SkipVought]

Maybe start with I = 1

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[Andrzejek]

Try this:

Sub Workbook_Open()
      
Dim s As Worksheet
[s]Dim varSelect[/s]
Dim i As Integer
[s]i = 0[/s]
[s]Sheet1.Activate[/s]
[s]Set varSelect = Sheet26.ComboBox1[/s]
  
[s]With varSelect[/s]
For Each s In Worksheets
    Select Case s.Name
        Case "April", "May", "June" [green]'etc [/green]
        Case Else
            Sheet26.ComboBox1.AddItem s.Name, I
            i = i + 1
    End Select
Next s
[s]End With[/s]

ActiveWorkbook.Sheets("Print").Activate
Range("A1").Select

End Sub

Note the re-position of [tt]i = i + 1[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[mar050703]

Thanks Skip

I am still getting the same error and the same line is yellow.

I have placed the code as follows

 varSelect.AddItem s.Name, I

 

[Andrzejek]

What happens if you would just have this:
[tt].AddItem s.Name[/tt]

Have fun.

---- Andy

A bus station is where a bus stops. A train station is where a train stops. On my desk, I have a work station.

 

[mar050703]

Thanks, the change of code has worked.

Perfect.

 

[SkipVought]

Please be more specific for the benefit of other members interested in a good solution.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mar050703]

Sorry,

Andy's code was perfect.

Sub Workbook_Open()
      
Dim s As Worksheet
Dim i As Integer


 For Each s In Worksheets
    Select Case s.Name
        Case "April", "May", "June" 'etc 
        Case Else
            Sheet26.ComboBox1.AddItem s.Name, I
            i = i + 1
    End Select
Next s

ActiveWorkbook.Sheets("Print").Activate
Range("A1").Select

End Sub

 

[strongm]

This is a little more succint:

[blue]Private Sub Workbook_Open()
    Dim s As Worksheet
    For Each s In Worksheets
       If InStr("January February March April May June July August September October November December", s.Name) = 0 Then Sheet1.ComboBox1.AddItem s.Name
    Next s
End Sub[/blue]