Sub abc()
Dim ole As OLEObject
Dim cbox As MSForms.Combobox

Set ole = ActiveSheet.OLEObjects.Add( _
ClassType:="Forms.ComboBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=67.5, _
Top:=275.25, _
Width:=63, _
Height:=40.5)
Set cbox = ole.Object
cbox.Name = "MyCombo"
ole.ListFillRange = "Sheet1!A1:A10"
cbox.ListIndex = 3
Msg = "MyCombo has a value of " & _
ActiveSheet.OLEObjects("MyCombo").Object.Value
MsgBox Msg

End Sub


--
Regards,
Tom Ogilvy




wrote in message
oups.com...
Apologies for sort of a newbee question.

I want to

1. place a combo box onto a worksheet,
2. populate it with some values,
3. execute some VBA upon selection.

Creating a DialogBox with a ComboBox isn't an option. It has to be on a
Sheet.

The recorder creates the ComboBox as :

ActiveSheet.OLEObjects.Add(ClassType:="Forms.Combo Box.1",
Link:=False, _
DisplayAsIcon:=False, Left:=67.5, Top:=275.25, Width:=63,
Height:= _
40.5).Select

Once ActiveSheet is set, how do is this ComboBox accessed using VBA?

And how do I add an option item? And get the selection.

Thanks.