Inserting combo box into a specific range/cell using vba?

  • Thread starter Thread starter rjre
  • Start date Start date
R

rjre

morning all,

is it possible for me to insert a combo box into a specfic range/cell
using vba?

thanks

richard
 
This will insert a ComboBox from the forms tool bar.
Inserting the combobox into B1:C1
use A1:A9 as the input range
F1 as the linked cell.

Sub InsertComboBox()
With Range("B1:C1")
Set Comb = ActiveSheet.DropDowns.Add(.Left, .Top, .Width, .Height)
End With
With Comb
..ListFillRange = "$A$1:$A$9"
..LinkedCell = "$F$1"
End With

End Sub
 
There's a combobox from the control toolbox toolbar and a dropdown from the
Forms toolbar.

Each behave differently, but to add them, you can use:

Option Explicit
Sub testme()

Dim OLEObj As OLEObject
Dim myDD As DropDown
Dim myRng As Range

'combobox from the Control toolbox toolbar
Set myRng = ActiveSheet.Range("a1:c1") '3 cells
With myRng
Set OLEObj = .Parent.OLEObjects.Add _
(ClassType:="Forms.ComboBox.1", _
Link:=False, _
DisplayAsIcon:=False, _
Left:=.Left, _
Top:=.Top, _
Width:=.Width, _
Height:=.Height)
End With

'Dropdown from the Forms toolbar
Set myRng = ActiveSheet.Range("a3:c3") '3 cells
With myRng
Set myDD = .Parent.DropDowns.Add _
(Left:=.Left, _
Top:=.Top, _
Width:=.Width, _
Height:=.Height)
End With

End Sub

Don't step through the Control Toolbox Toolbar code. You'll get an error.
 
Back
Top