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)

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

Open in new window

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

Open in new window

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