Looping thru ComboBoxes on a Worksheet
 

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

Looping thru ComboBoxes on a Worksheet
 

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 wrote:

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

Looping thru ComboBoxes on a Worksheet
 

On May 13, 10:40 am, Dave Peterson wrote:
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 wrote:

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

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?

Looping thru ComboBoxes on a Worksheet
 

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
--
HTH...

Jim Thomlinson


"AJ Master" wrote:

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

Looping thru ComboBoxes on a Worksheet
 

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

--
HTH...

Jim Thomlinson


"AJ Master" wrote:

On May 13, 10:40 am, Dave Peterson wrote:
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 wrote:

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

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?