Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 505
  • Last Modified:

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.
0
hindersaliva
Asked:
hindersaliva
  • 2
  • 2
1 Solution
 
Rory ArchibaldCommented:
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now