Loop Through Combo Boxes on Worksheet and in Workbook

[AJ Master]

I have created a workbook that compares 3 scenarios for cost
sensitivity. Each workbook has 3 worksheets with 8 combo boxes each
and a summary worksheet. For example, on worksheet 1 combo boxes 1,
3, 5, and 7 all have the same list population. This would be same for
worksheet 2 and 3 as well. Can anyone show me how to loop through
combo boxes 1, 3, 5, 7 and then do the same on worksheets 2 and 3? I
know the hard way, but is there a simpler way?

Also, if it helps the list population in each combo box are the years
1995 to 2020.

Thanks...AJ

 

[Vergel Adriano]

Assuming you're using the combo box from the Forms Toolbar, and assuming that
the combo boxes you're interested in are using the same Input Range, you can
try something like this:

Sub test()
Dim dropdown As Object

For Each dropdown In Worksheets("Sheet1").DropDowns
If dropdown.ListFillRange = "Sheet2!$A$1:$A$10" Then
MsgBox "Do something"
End If
Next dropdown
End Sub

 

[Peter T]

Sub AddCombos()
' add a new wb with 8 combo's on each sheet
Dim i As Long, j As Long
Dim ws As Worksheet
Dim ole As OLEObject

For Each ws In Workbooks.Add.Worksheets
Application.ScreenUpdating = False
For i = 0 To 3
For j = 0 To 1
Set ole = ws.OLEObjects.Add("Forms.ComboBox.1")
With ole
.Left = 20 + j * 200
.Top = 20 + i * 50
End With
Next
Next
Next
Application.ScreenUpdating = True
End Sub

Sub PopCombos()
Dim i As Long, j As Long, n As Long
Dim ole As OLEObject
Dim arr(1995 To 2020)

For i = 1995 To 2020
arr(i) = i
Next

For i = 1 To 3
n = 0
For Each ole In ActiveWorkbook.Worksheets(i).OLEObjects
If InStr(ole.ProgId, "ComboBox") Then
n = n + 1
If n > 7 Then Exit For
If n Mod 2 Then
ole.Object.List = arr
End If
End If
Next
Next

End Sub

Would seem more logical to do all at the same time rather than as above, but
that seems to be the way you want to do it. The sub AddCombos was for my
testing but you might as well have it too.

Regards,
Peter T

 

[AJ Master]

Assuming you're using the combo box from the Forms Toolbar, and assuming that
the combo boxes you're interested in are using the same Input Range, you can
try something like this:

Sub test()
Dim dropdown As Object

For Each dropdown In Worksheets("Sheet1").DropDowns
If dropdown.ListFillRange = "Sheet2!$A$1:$A$10" Then
MsgBox "Do something"
End If
Next dropdown
End Sub

Vergel,

I am using the combo box from the control toolbox toolbar. My
previous experience with using the "ListFillRange" has not been good
as I've found excel behaves quite strangely when using this. This is
why I populate the combo box through vba. I know that others
including Tom Ogilvy have commented about this issue and the work
around seems to be to avoid the "ListFillRange". I have written an
array to fill the combo boxes but I need help to populate only
Combobox1, Combobox3, Combobox5 and Combobox7 on worksheet 2. I then
need to do the same for worksheet 3 and worksheet 4 (e.g. "sheet2",
"sheet3", "sheet4"...and these are the names in vba not on the tab.

Thanks.....AJ

 

[AJ Master]

Sub AddCombos()
' add a new wb with 8 combo's on each sheet
Dim i As Long, j As Long
Dim ws As Worksheet
Dim ole As OLEObject

For Each ws In Workbooks.Add.Worksheets
Application.ScreenUpdating = False
For i = 0 To 3
For j = 0 To 1
Set ole = ws.OLEObjects.Add("Forms.ComboBox.1")
With ole
.Left = 20 + j * 200
.Top = 20 + i * 50
End With
Next
Next
Next
Application.ScreenUpdating = True
End Sub

Sub PopCombos()
Dim i As Long, j As Long, n As Long
Dim ole As OLEObject
Dim arr(1995 To 2020)

For i = 1995 To 2020
arr(i) = i
Next

For i = 1 To 3
n = 0
For Each ole In ActiveWorkbook.Worksheets(i).OLEObjects
If InStr(ole.ProgId, "ComboBox") Then
n = n + 1
If n > 7 Then Exit For
If n Mod 2 Then
ole.Object.List = arr
End If
End If
Next
Next

End Sub

Would seem more logical to do all at the same time rather than as above, but
that seems to be the way you want to do it. The sub AddCombos was for my
testing but you might as well have it too.

Regards,
Peter T

Peter,

Thanks for your reply. I have already added the combo boxes so I
didn't need the sub as you may have guessed. I only need to populate
combo boxes 1, 3, 5 and 7 and I'll try your code, not that I
understand it to well, but I'll learn I'm sure


AJ

 

[Peter T]

Peter,

Thanks for your reply. I have already added the combo boxes so I
didn't need the sub as you may have guessed. I only need to populate
combo boxes 1, 3, 5 and 7 and I'll try your code, not that I
understand it to well, but I'll learn I'm sure


AJ

If you want to link to cells remove the array stuff and change:

ole.Object.List = arr
to
ole.ListFillRange = "Sheet1!G1:G31"
or
ole.ListFillRange = '"years"

where "years" is a Named range.

Regards,
Peter T