CODEWITHSUNDEEP
excelvbacombobox
Foxhunt
Foxhunt

Reputation: 870

Create writable combobox with vba

I want to create a combobox on the cell the user click, and permit the user to write and/or choose (an autocompletion similar to google), set it to the cell and close combobox.

I have this code which create the box :

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lst
If Not Intersect([4:7], Target) Is Nothing And Target.Count = 1 Then
        Me.DropDowns.Delete
        With Worksheets("Listing")
            lst = "'" & .Name & "'!" & _
              .Range(.Range("a1"), _
              .Cells(.Rows.Count, 1).End(xlUp)).Address()
         End With
        With Me.Shapes.AddFormControl(xlDropDown, Left:=Target.Left, _
                                  Top:=Target.Top, Width:=60, Height:=15)
             .Name = "CB"
            .OnAction = "CB_Change"
            .ControlFormat.ListFillRange = lst

        End With
    End If
End Sub

The problem is that the Comboboxes created only allow user to select and not to write into

Upvotes: 1

Views: 886

Answers (2)

Sam
Sam

Reputation: 5721

If possible, use Data Validation instead. This will create a writable combo, based on the values in cells J3:J5

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If Not Intersect([4:7], Target) Is Nothing And Target.Count = 1 Then
        With Target.Validation
            .Delete
            .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=$J$3:$J$5"
            .IgnoreBlank = True
            .InCellDropdown = True
            .InputTitle = ""
            .ErrorTitle = ""
            .InputMessage = ""
            .ErrorMessage = ""
            .ShowInput = True
            .ShowError = False
        End With

        ' This will expand the list
        Target.Select
        SendKeys "%{DOWN}"
    End If
End Sub

Upvotes: 2

Mathieu Guindon
Mathieu Guindon

Reputation: 71187

ActiveX controls are easier to configure than Form Controls. MSForm.ComboBox controls have a Style property that, when set to fmStyleDropDownCombo, makes the textbox area editable; when the style is fmStyleDropDownList, user input is restricted to what's in the dropdown list.

ComboBox1 properties

...with that said, Data Validation is probably a better option (less bug-prone, better integrated with the worksheet/Excel, no MSForm dependency), if you can live with "circle invalid data" making red circles around "invalid data" that is allowed to be entered, but not in the dropdown list:

DV red circle around invalid data

Upvotes: 1

Related Questions