Looping through all comboboxes in workbook and setting values with .additem

[Mike]

Hi Folks
I want to loop through all comboboxes when my workbook opens and set
the values for them. I can loop through them using the oleobjects
collection but that doesn't support the .additem method. I can also
loop through the shapes collection and access them, but again can't
use .additem I am going to have about 100 combo boxes spread over many
sheets with the same values. Being able to loop through and set the
values will save a ton of time. I've tried lots of help resources and
headscratching and still can't figure this one out.
Thanks
Mike

 

[Dave Peterson]

You should be able to use .additem.

This worked ok for me:

Option Explicit
Sub auto_open()

Dim OLEObj As OLEObject
Dim wks As Worksheet
Dim iCtr As Long

For Each wks In ThisWorkbook.Worksheets
For Each OLEObj In wks.OLEObjects
If TypeOf OLEObj.Object Is MSForms.ComboBox Then
For iCtr = 1 To 10
OLEObj.Object.AddItem "A" & iCtr
Next iCtr
End If
Next OLEObj
Next wks

End Sub

 

[Mayank Gupta]

Mike,

If the Ole Objects does not work, try difining the comboboxes as dropdowns.
Use the following code:

Sub FillDropDowns()

Dim ddDropDown as DropDown
Dim wsSheet As Worksheet
Dim intCount as Integer

For Each wsSheet in ThisWorkbook.Worksheets
For Each ddDropDown in wsSheet.DropDowns
For intCount=1 to maxNumInputs
ddDropDown.AddItem <item>
Next intCount
Next ddDropDown
Next wsSheet

End Sub