Looping thru ComboBoxes on a Worksheet

[AJ Master]

Hi All,

I checked lots of posts but have not been able to find a solution for
my looping problem. Hope someone can help. I have 8 comboboxes on a
worksheet sheet and I want to reset the listindex on each one to 0.
Here's the code I used:

Application.ScreenUpdating = False

Dim sheetname As String
Dim sFormName As String

Dim a As Byte
Dim b As Byte

sheetname = ActiveSheet.Name

For a = 1 To 8
Set sFormName = "combobox" & a
Worksheets(sheetname).sFormName.ListIndex = 0
Next a

When I run the code I get "run-time error '91'" which states that
"Object variable or with Block variable not set" This occurs when its
starts to run the for loop. Can anyone tell me what I'm doing wrong?

AJ

 

[Dave Peterson]

Dim iCtr As Long
With ActiveSheet
For iCtr = 1 To 8
.OLEObjects("Combobox" & iCtr).Object.ListIndex = 0
Next iCtr
End With

or if you didn't count the number of comboboxes, but wanted to get all of them.

Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
OLEObj.Object.ListIndex = 0
Next OLEObj

Remember .listindex = 0 will choose the first option. .listindex = -1 will
clear it.

 

[AJ Master]

Dim iCtr As Long
With ActiveSheet
For iCtr = 1 To 8
.OLEObjects("Combobox" & iCtr).Object.ListIndex = 0
Next iCtr
End With

or if you didn't count the number of comboboxes, but wanted to get all of them.

Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
OLEObj.Object.ListIndex = 0
Next OLEObj

Remember .listindex = 0 will choose the first option. .listindex = -1 will
clear it.

Dave,

Thanks much. I also have 10 checkboxes that I need to set to true so
I don't think the 2nd option would work since listindex isn't a
property for that object. Right?

 

[Jim Thomlinson]

Something like this perhaps...

Dim wks As Worksheet
Dim lng As Long

Set wks = ActiveSheet

For lng = 1 To 8
wks.OLEObjects("ComboBox" & lng).Object.ListIndex = 0
Next lng

 

[Jim Thomlinson]

Sub test()
Dim OLEObj As OLEObject
For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is MSForms.ComboBox Then
OLEObj.Object.ListIndex = 0
ElseIf TypeOf OLEObj.Object Is MSForms.CheckBox Then
OLEObj.Object.Value = False
End If
Next OLEObj
End Sub