I want to iterate over several ComboBox objects in the same Worksheet sht
:
Dim obj As ComboBox
Dim sht as Worksheet
...
For Each obj In Array(sht.ComboBox1, sht.ComboBox2)
Next obj
Runtime error 424: object required (raised at
For Each ...
)
I also tried to address the objects by iterating over the names:
Dim s as Variant
Dim obj As ComboBox
Dim sht as Worksheet
...
For Each s In Array("ComboBox1", "ComboBox2")
obj = CallByName(sht, s, VbGet)
Next s
Runtime error 91: Object variable or With block variable not set. (raised at
obj = ...
)
What is the correct way?
And why don't these approaches work as I would expect them to?
Prepending Set
as suggested by @KostasK. to the assignment works:
Dim s as Variant
Dim obj As ComboBox
Dim sht as Worksheet
...
For Each s In Array("ComboBox1", "ComboBox2")
Set obj = CallByName(sht, s, VbGet)
Next s
The ComboBox is part of Worksheet.OLEObjects
. In this case obj
must be declared as type OLEObject
to work (as long as Option Explicit
is set)
Dim s as Variant
Dim obj As OLEObject
Dim sht as Worksheet
...
For Each s In Array("ComboBox1", "ComboBox2")
Set obj = sht.OLEObjects(s)
Next s