Excel VBA Combobox object syntax help

I have a Class Module that needs to fill a combobox. It works fine when I hard code the combobox like
Dim cmbCombo as ComboBox
Set cmbCombo = ActiveSheet.cmbMyCombo1

Now I want to pass the string "cmbMyCombo1" to the object (from this class) so I can address any specified combo on the sheet. How do I modify the above?

I tried ... and didn't work
Set cmbCombo = ActiveSheet.Shapes.Range(Array(m_comboname))
(m_comboname is the local variable holding "cmbMyCombo1" and I got that syntax from the macro recorder, which worked for .Left for example)

Thanks.
hindersalivaAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
Try:
Set cmbCombo = ActiveSheet.OLEObjects(m_comboname).Object

Open in new window

0
 
Rory ArchibaldCommented:
Or:
   Set cbo = CallByName(ActiveSheet, m_comboname, VbGet)

Open in new window

0
 
hindersalivaAuthor Commented:
Wow Rory! Masterful!!!!
Thanks. Exactly right.
0
 
hindersalivaAuthor Commented:
The CallByName works also.
Never knew that one!
Thanks.
0
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.