Referencing ComboBox indirectly using variable

[baronvont]

Hi,

I am trying to reference a particular combobox in excel.

I know you can use a variable to reference cells etc such as:

For var = 1 to 10
range("A" & var).select
....
next

but what about a combobox. I need to be able to change the value in a particular combobox out of a range of boxes, but this won't work (ie need to get to combobox5):

var = 5
combobox & var = somevalue
nor will
"combobox" & var = somevalue

So has anyone got a bright idea?

thanks
GT

 

[rmikesmith]

GT,

Assuming your combobox is on a Userform, try something like this:

var = 5
UserForm1.Controls("ComboBox" &var).Value = Somevalue

Regards,
Mike

 

[baronvont]

No, it's on the sheet it'self. There are 90 combos in total (3 per row, one row per month) combobox1-90

Thanks
Georg

 

[rmikesmith]

Georg,

OK. Try the following, then:

var = 5
Worksheets("SheetName").OLEObjects("ComboBox" & var).Object.Value = SomeValue

Mike

 

[baronvont]

Mike

Not quite - came up with 'Subscript out of range'
After I changed it to this it worked...

Activesheets.OLEObjects("ComboBox" & var).Object.Value

Many thanks
Georg