Adding Sheets to a combo Box

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" <[email protected]> wrote in message
news:[email protected]...
>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
news:[email protected]...
> 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" <[email protected]> wrote in message
> news:[email protected]...
>>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
>>
>
>

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" <[email protected]> wrote in message news:[email protected]...
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

Thanks Gary that worked perfectly.