I am trying to a sheets to a combo box without knowing the number of
sheets. I have tried this code:

For Each Sheet in ThisWorkBook
cmbComboBox1.AddItem Sheet.Name
Next

Any Ideas. Thanks
-David

Sorry thats add sheets to a combo box

this should work

Sub test()

Dim SH As Worksheet
For Each SH In Worksheets
i = i + 1
ComboBox1.AddItem "test" & SH.Name
Next
End Sub


--


Gary


"David Pick" wrote in message
oups.com...
I am trying to a sheets to a combo box without knowing the number of
sheets. I have tried this code:

For Each Sheet in ThisWorkBook
cmbComboBox1.AddItem Sheet.Name
Next

Any Ideas. Thanks
-David

you can get rid of the i=i+1 line, it was leftover from something else

--


Gary


"Gary Keramidas" <GKeramidasATmsn.com wrote in message
...
this should work

Sub test()

Dim SH As Worksheet
For Each SH In Worksheets
i = i + 1
ComboBox1.AddItem "test" & SH.Name
Next
End Sub


--


Gary


"David Pick" wrote in message
oups.com...
I am trying to a sheets to a combo box without knowing the number of
sheets. I have tried this code:

For Each Sheet in ThisWorkBook
cmbComboBox1.AddItem Sheet.Name
Next

Any Ideas. Thanks
-David

Thanks Gary that worked perfectly.

David,

A combo box can be added to a user form or a sheet.
On a userform, the code would be "UserForm1.cmbComboBox1.AddItem Sheet.Name
On a sheet there are two types of combo boxes you can add.
One from the forms toolbar...
One from the Control tool box...

It appears you have the second choice.
As it is with the above example, you have to tell Excel
where the control is. So if the code is in the module
belonging to the sheet holding the combobox, the code would be...
Me.ComboBox1.AddItem.AddItem Sheet.Name
-or-
If the code is in a standard module the code would be...
Worksheets("Sheet1").ComboBox1.AddItem Sheet.Name

Also, you would have a little cleaner code if you used a different
variable name for sheet...maybe "Sht".

Regards,
Jim Cone
San Francisco, USA


"David Pick" wrote in message oups.com...
I am trying to a sheets to a combo box without knowing the number of
sheets. I have tried this code:

For Each Sheet in ThisWorkBook
cmbComboBox1.AddItem Sheet.Name
Next

Any Ideas. Thanks
-David

correction...
Me.ComboBox1.AddItem.AddItem Sheet.Name
should be...
Me.ComboBox1.AddItem Sheet.Name

Jim Cone