Referencing OLE objects from within Excel VBA

[groston]

On a spreadsheet I am developing, I have placed four OLE ListBoxes. They are named, origianlly enough, lstData1 -> lstData4.

In my code, I would like to create an array that references these objects. The following code snippet works just fine:

Dim myListBoxes(4)
Set myListBoxes(1) = Worksheets("Control&quot.lstData1
Set myListBoxes(2) = Worksheets("Control&quot.lstData2
Set myListBoxes(3) = Worksheets("Control&quot.lstData3
Set myListBoxes(4) = Worksheets("Control&quot.lstData4

But, as you can tell, this is bad, ugly code!

First, exactly what type is the myListBoxes array?
Second, how can I loop through the controls on my worksheet to to this automatically?

This code snippet allowed me to find the LisBox objects:

Set wsCont = Worksheets("Control&quot
With wsCont.Shapes
For i = 1 To .count
For j = 1 To 4
If (.Item(i).Name = ("lstData" & Format(j))) Then
*** Set myListBoxes(j) = .Item(i)
End If
Next j
Next
End With

but the line marked *** doesn't work.

You suggestions gratefully welcomed!

Thanks.

 

[RobBroekhuis]

I think the following will do the trick:

for i=1 to ActiveSheet.OLEObjects.count
set myListBoxes(i)=activesheet.OLEObjects(i)
next i

if you only have listboxes. Or,

for i=1 to 4
set myListBoxes(i)=activesheet.OLEObjects("lstData" & format(i))
next i

The typename is OLEObject, so the proper dim is

dim myListboxes(4) as OLEObject


Rob

 

[groston]

Rob,

Thanks for the help. I think I had tried this earlier, but ran into another problem - are you willing to help once more?

What I had had in my code was this:

Dim myListBoxes(4)
Set myListBoxes(1) = Worksheets("Control&quot.lstData1
With myListBoxes(i)
.ColumnCount = 2
.ColumnWidths = "5;20"
(etc)
End With

Once I used your suggestion as declare myListBoxes as type OLEObject, and then set it to an object of this type, this code breaks with a "Object does not support this property or method" run-time error. (This also occurs if I do not specify the type of the object explicitly.)

How can I get this to work?

Thanks!

 

[RobBroekhuis]

Ah yes,
I responded too quickly. What you want is:

dim myListBoxes(4) as ListBox
for i=1 to 4
set myListBoxes(i)=activesheet.OLEObjects("lstData" & format(i)).Object
next i

Rob

 

[groston]

Rob,

Thanks for your help.
I tried what you suggested - and it almost worked. Changing the declaration of myListBoxes from ListBox to Object made everything work as expected.