ComboBox on a Sheet

[gimme_this_gimme_that]

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.ComboBox.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.

 

[Tom Ogilvy]

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

 

[gimme_this_gimme_that]

This works great.

But is it possible to add the items to the ole object from within VBA
(using an assignment statement) rather than from a range.

Thanks!

 

[Tom Ogilvy]

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

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"
for each cell in Range("sheet1!A1:A10")
ole.Object.AddItem cell.Value
Next
cbox.ListIndex = 3
Msg = "MyCombo has a value of " & _
ActiveSheet.OLEObjects("MyCombo").Object.Value
MsgBox Msg

End Sub