VBA Questions

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

me.controls doesn't work in VBA.

At least i keep getting an error

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

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?