Using variables to reference a combobox

[baronvont]

Hi,

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

For var = 1 to 10
range("A" & var) - dosomethin
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

 

[SkipVought]

Hi,

Are you on a worksheet, in a UserForm? What application?

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[baronvont]

Hi Skip, it's excel. The boxes are on the sheet - 3 per row, 31 rows - 93 combl boxes labeled combobox1-90

Thanks
Georg

 

[SkipVought]

Take a look at

ActiveSheet.Shapes("YourShapeName")

where YourShapeName can be a variable.

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[SkipVought]

oops...

sComboName = "Combobox" & i

with ActiveSheet.Shapes(sComboName)
'manipulate this shapes properties
end with

Skip,
[sub]
[red]A palindrome gone wrong?[/red]
A man, a plan, a ROOT canal...
PULLEMALL![/sub]

 

[baronvont]

Skip,

Thanks, but looks like shapes properties don't include .value, only format properties

I tried messing with
Worksheets("SheetName").OLEObjects("ComboBox" & var).Object.Value
on advice from another guy but comes up with 2Suvscript out of range'...

Georg

 

[baronvont]

Ok it was close..

After I changed it to this it worked...

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

Many thanks
Georg