VBA Questions

[ghoff12]

I have two questions...both of them are in VBA

1) Is there a way i can embed a combo box directly in the cell. so it the combobox is exactly the same size as the cell it needs to go in.

2) the other quesiton is, since i can't use control arrays in VBA (at least i don't think so) is there an easy way to reference around 30 combo boxes.

IF yes, can i get an example.

thanks!

Dim Ctrl As ComboBox

For Each Ctrl In Me.Controls
If TypeOf Ctrl Is ComboBox Then
'do stuff here
End If
Next

[ghoff12]

me.controls doesn't work in VBA.

At least i keep getting an error

[HarryW]

Hmm... That's strange, under the UserForm class in the object browser there is clearly a controls collection. Umm... that's odd, I take it you are writing this in a userform's code, not in a module? ie. the keyword 'Me' is valid?

sorry had it wrong, it should be like this:


Dim ctrl As Control

For Each ctrl In Me.Controls
If TypeOf ctrl Is ComboBox Then
ctrl.(someproperty here)
End If
Next
End Sub

[ghoff12]

I haven't tried your suggestion, but will this work in a worksheet. I don't have a user form. The combo boxes are placed directly on a spreadsheet over a cell.

Don't know if this makes a difference, but i'll try it.

any ideas?