Adding items to ComboBox in a loop?

[cmpcwil2]

Using vb comboBox is it possible to add the same items to numerous comboBox on the same sheet?
I have been trying the following....

Dim mycntrl As OLEObject
Dim sht As Worksheet
Set sht = ActiveSheet

For Each mycntrl In sht.OLEObjects

mycntrl.addItem "item1"

Next mycntrl

However I noticed that the addItem method is not available when trying this, so how would you add an item in a loop?

thank you in advance for any help

[Doug Glancy]

cmpcwil2,

This syntax is difficult for me to remember, but I think this is what you
want:

Dim mycntrl As OLEObject
Dim sht As Worksheet

Set sht = ActiveSheet
For Each mycntrl In sht.OLEObjects
If TypeOf mycntrl.Object Is ComboBox Then
mycntrl.Object.AddItem "item1"
End If
Next mycntrl

hth,

Doug


"cmpcwil2" <[email protected]> wrote in
message news:[email protected]...
>
> Using vb comboBox is it possible to add the same items to numerous
> comboBox on the same sheet?
> I have been trying the following....
>
> Dim mycntrl As OLEObject
> Dim sht As Worksheet
> Set sht = ActiveSheet
>
> For Each mycntrl In sht.OLEObjects
>
> mycntrl.addItem "item1"
>
> Next mycntrl
>
> However I noticed that the addItem method is not available when trying
> this, so how would you add an item in a loop?
>
> thank you in advance for any help
>
>
> --
> cmpcwil2
> ------------------------------------------------------------------------
> cmpcwil2's Profile:
> http://www.excelforum.com/member.php...o&userid=33411
> View this thread: http://www.excelforum.com/showthread...hreadid=538425
>

[Tom Ogilvy]

Dim mycntrl As OLEObject
Dim sht As Worksheet
Set sht = ActiveSheet

For Each mycntrl In sht.OLEObjects
if typeof mycntrl.object is MSforms.Combobox then
mycntrl.Object.addItem "item1"
end if

Next mycntrl

--
Regards,
Tom Ogilvy


--
Regards,
Tom Ogilvy



"cmpcwil2" wrote:

>
> Using vb comboBox is it possible to add the same items to numerous
> comboBox on the same sheet?
> I have been trying the following....
>
> Dim mycntrl As OLEObject
> Dim sht As Worksheet
> Set sht = ActiveSheet
>
> For Each mycntrl In sht.OLEObjects
>
> mycntrl.addItem "item1"
>
> Next mycntrl
>
> However I noticed that the addItem method is not available when trying
> this, so how would you add an item in a loop?
>
> thank you in advance for any help
>
>
> --
> cmpcwil2
> ------------------------------------------------------------------------
> cmpcwil2's Profile: http://www.excelforum.com/member.php...o&userid=33411
> View this thread: http://www.excelforum.com/showthread...hreadid=538425
>
>

[cmpcwil2]

Thank you for you help