ComboBox on a Sheet
 

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.

ComboBox on a Sheet
 

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.

ComboBox on a Sheet
 

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!

ComboBox on a Sheet
 

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

--
Regards,
Tom Ogilvy


wrote in message
oups.com...
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!