Programmatically create combo boxes

[Richard]

Does anyone know how to create a combo box in the cell the user clicks on?

If that can't be done, does anyone know how to programmatically create and
array of comboboxes and align them in a column?

Thanks in advance.

 

[Dave Peterson]

You could create and destroy them on the fly, but maybe just creating one and
then moving it where you want it would be sufficient.

I'm not sure if you used a combobox from the Control toolbox toolbar or a
DropDown from the Forms toolbar, so toss the portion you don't want and
uncomment the other.

rightclick on the worksheet tab that should have this behavior and select view
code. Paste this in the codewindow:

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

' If Intersect(Target, Me.Range("a:a")) Is Nothing Then
' Me.ComboBox1.Visible = False
' Exit Sub
' End If
' Me.ComboBox1.Visible = True
' With Me.ComboBox1
' .Left = Target(1).Left
' .Top = Target(1).Top
' .Width = Target(1).Width
' .Height = Target(1).Height
' End With
'
' If Intersect(Target, Me.Range("a:a")) Is Nothing Then
' Me.DropDowns("drop down 1").Visible = False
' Exit Sub
' End If
' Me.DropDowns("drop down 1").Visible = True
' With Me.DropDowns("drop down 1")
' .Left = Target(1).Left
' .Top = Target(1).Top
' .Width = Target(1).Width
' .Height = Target(1).Height
' End With

End Sub