Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
RowSource for Sheet ComboBox | Excel Worksheet Functions | |||
How do I put hyperlinks to another sheet in a combobox? | Excel Discussion (Misc queries) | |||
Sheet combobox help | Excel Worksheet Functions | |||
combobox/copying sheet | Excel Discussion (Misc queries) | |||
ActiveX ComboBox Control on Sheet | Excel Programming |