Excel VBA combo box control

I got an issue which my VBA script in excel that i need some expert help here.
The situation is i have 20 combo box added in the excel worksheet which i group as the same category such cboxName1, cboxName2,...cboxName20 because i could not make an array control box. Then I want to write a small routine which i can use loop function to read all the value in these control using a variable to identify the specify combo box ? For example whati like to do is as below but that does not work  :
For i = 1 to maxcnt
 var & i = worksheets(1).comboName & i  ".value"
next i

please help me with my problem Thank you.
Who is Participating?
StellanRosengrenConnect With a Mentor Commented:
Sub tst()
    Dim i As Integer
    For i = 1 To 3
        Debug.Print Worksheets(1).OLEObjects("ComboBox" & i).Object.Value
    Next i
End Sub
StellanRosengrenConnect With a Mentor Commented:
ComboBox is part of the OLEObjects collection. You can use the name of the control to select each item. In my example the comboboxes are named "ComboBox1", "ComboBox2", "ComboBox3". The properties of the control are returned by the Object property of the OLEObject.

RobSampsonConnect With a Mentor Commented:
Stellan, that's perfect!  Then you could something like this, yeah:
    ReDim arrValues(intMaxCnt)
    For intCount = 1 To intMaxCnt
        arrValues(intCount) = Worksheets(1).OLEObjects("ComboBox" & i).Object.Value
    MsgBox Join(arrValues, vbCrLf)


netcoolAuthor Commented:
It is exactly what i need, thank everyone.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.