Search code examples
excelvbaobjectcomboboxcontrols

How to cast worksheet type variable into Sheet1 type Object instance

There are 8 sheets with several different ActiveX comboboxes in each. Some comboboxes are able to filter the list of options of remain comboboxes. The logic behind controls are rather similar between the sheets. So I am trying to write common code in a separate module. And faced a problem:

dim ws as Worksheet
set ws = Sheet1

'can't get members of object Sheet1 from Worksheet type object
ws.Combobox1.List = arr

Should I write handlers in each sheet module to get access to .List? Or may be someone knows tricks on how to correctly manipulate controls from any module for any sheet?

By now I tend to use select cases everywhere and hardcode the sheet name to reference the object like this:

Sheet1.Combobox1.List = arr
Solution

  • I believe your issue lies in which module you are using. When not using one of the ComboBox event modules, I've found that the combobox will not be detected using:

    ComboBox1.List(1)

    Instead referring to the OLEObject worked for me:

    ws.OLEObjects("ComboBox1").Object.List(1)

    Hopefully this works for you.