Looping through many ComboBoxes by name

Question

I have quit a lot ComboBox objects, and I am trying to display the value of the ComboBox by looping through them:

For i=1 to 100
    MsgBox ("ComboBox" & i & ".Text")
Next

Of course this code is not working because of the quotes, but when I remove them, then I have a compilation error.

How do I do this?

This is related: http://stackoverflow.com/questions/25266267/looping-through-multiple-command-buttons-to-change-their-properties-based-on-cel/25266667#25266667 — Jean-François Corbett, Aug 13 '14 at 09:15
Or this: http://stackoverflow.com/questions/12333789/how-to-get-a-name-of-control-by-name — Adriano Repetti, Aug 13 '14 at 09:17
@AdrianoRepetti: [the question you link to](http://stackoverflow.com/questions/12333789/how-to-get-a-name-of-control-by-name) is related, but it certainly isn't a duplicate, if you look carefully. — Jean-François Corbett, Aug 13 '14 at 09:26
@Jean-FrançoisCorbett he needs "get_object_by_name" function, same as in that question and same answer too. — Adriano Repetti, Aug 13 '14 at 09:29
@Jean-FrançoisCorbett yes, it does in the accepted answer (he's comparing .Text property with a literal)... — Adriano Repetti, Aug 13 '14 at 10:33
@AdrianoRepetti: I stand corrected. Still I think this question is different enough; the other question's answer is not easy to convert to a solution for this question for someone maybe not expert in VBA which I'm guessing the OP isn't. — Jean-François Corbett, Aug 13 '14 at 12:20
@Jean-FrançoisCorbett IMO a question doesn't need to be **exactly** the same (like test copy & paste) to be closed as dup (especially because each one will write a different code example and will need to solve a slightly different problem). That's why I would encourage first to **search and learn from a different question** but with exact same answer. Anyway...that's why close votes are _votes_! ;) — Adriano Repetti, Aug 13 '14 at 12:26

score 3 · Accepted Answer · answered Aug 13 '14 at 10:17

The OP does not state if the objects in question are ActiveX or Form Control objects.

To handle both object types, and if the objects names are not ComboBox*, try this

Sub Demo()
    Dim ws As Worksheet
    Dim shp As Shape
    Dim cb As ComboBox

    Set ws = ActiveSheet

    For Each shp In ws.Shapes
        With shp
            Select Case .Type
                Case msoFormControl
                    If .FormControlType = xlDropDown Then
                        If .ControlFormat.Value = 0 Then
                            MsgBox .Name & " = "
                        Else
                            MsgBox .Name & " = " & .ControlFormat.List(.ControlFormat.Value)
                        End If
                    End If
                Case msoOLEControlObject
                    If .OLEFormat.progID = "Forms.ComboBox.1" Then
                        Set cb = .OLEFormat.Object.Object
                        MsgBox cb.Name & " = " & cb.Value
                    End If
            End Select
        End With
    Next
End Sub

+1 I like that you deal with the form controls as well. Bonus points if you explain what `.Object.Object` does? — Jean-François Corbett, Aug 13 '14 at 10:30
+1 :) @Jean-FrançoisCorbett: See [THIS](http://books.google.co.in/books?id=KZ4u-fS_N7UC&pg=PA442&lpg=PA442&dq=OLEFormat.Object.Object+vba+excel&source=bl&ots=xoPgc6oI_i&sig=JaMc76tgM5muxqPqNdEK1vgu5_Y&hl=en&sa=X&ei=FljrU5CuCNTl8AW93ILoBg&ved=0CBoQ6AEwADgK#v=onepage&q=OLEFormat.Object.Object%20vba%20excel&f=false) — Siddharth Rout, Aug 13 '14 at 12:22

score 1 · Answer 2 · edited May 23 '17 at 11:51

1

This works for me. Assumes your comboboxes' names are indeed ComboBox1 through ComboBox100.

Dim cb As ComboBox
Dim i As Long

For i = 1 To 100
Set cb = Sheet1.Shapes("ComboBox" & i).OLEFormat.Object.Object ' Ouch!
    MsgBox cb.Text
Next i

The .Object.Object trick I got from here.

edited May 23 '17 at 11:51

Community

1
1

answered Aug 13 '14 at 09:19

Jean-François Corbett

37,420
30
139
188

Looping through many ComboBoxes by name

2 Answers2

Linked