referencing multiple controls using a variable

[drabbacs]

I am trying to write a sub that will perform a common action on
multiple comboboxes or listboxes that I wish to refer to through the
use of a variable that I can 'build' in code and then loop. These
controls are located directly on a spreadsheet.

For example let's say I have 3 listboxes ListBox1, ListBox2,
ListBox3

I'd like to do something like

Dim a as string
a = "listbox" & x
for x = 1 to 3
a.backcolor = 1
next x

I've tried dim a as string, variant, listbox, control and none work.

I've tried a= and set a = . with no success.

I've tried listboxes(a).backcolor and that doesn't work.

Is there a way to do this?

Thanks in advance
Drabbacs

 

[jamescox]

There may be a better way, but this works.

(Color 'family' generator - the RGB part - thrown in for free :Bgr )

Public Sub dev3()

Dim sName As String
Dim iI As Integer

For iI = 1 To 3
sName = "ListBox" & CStr(iI)
ActiveSheet.OLEObjects(sName).Object.BackColor = RGB(80 * iI
80 * 11, 80 * (3 - iI))
Next iI

End Su