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

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

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

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


Mike wrote:

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

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

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


"Mike" wrote in message
oups.com...
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