Excel VBA ComboBox Match Required

Most probably you didn’t understand the title: Excel VBA ComboBox Match Required. I also didn’t understand it the first time I encountered the words. Actually, it just means that we allow a selection from the combobox drop down list only and the user cannot add his own data using the combo-box. In short, how to ensure that a user selects only a value displayed by the drop down list of a combobox. No other entry is allowed, for example, through manual typing in the combobox.

Watch the video to understand the concept:

 

Watch this video on YouTube.

Here’s the complete VBA code on how to ensure entry of data only from combobox drop down list. The combobox1_change procedure does not allow the user to enter any data of his own. He is forced to select only data from the drop down list which we get through a looping process into the combobox1.

Private Sub ComboBox1_Change()
Dim i As Long, LastRow As Long
LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row
If ComboBox1.ListIndex = -1 Then
Me.ComboBox1.SetFocus
MsgBox “Pls select a value from the drop down list!”
Exit Sub
End If
For i = 2 To LastRow
If Sheets(“Sheet1”).Cells(i, “A”).Value = Me.ComboBox1 Then
Me.TextBox1 = Sheets(“Sheet1”).Cells(i, “B”).Value
End If
Next
End Sub

The combobox1-dropwdownbuttonclick procedure gets the data from the worksheet and also updates the data displayed if more data is added using a looping process.

Private Sub ComboBox1_DropButtonClick()
Dim i As Long, LastRow As Long
LastRow = Sheets(“Sheet1”).Range(“A” & Rows.Count).End(xlUp).Row

If Me.ComboBox1.ListCount = 0 Then
For i = 2 To LastRow
Me.ComboBox1.AddItem Sheets(“Sheet1”).Cells(i, “A”).Value
Next i
End If

End Sub

 

Further reading:

Populating a combo-box with values

Populate combobox with unique items

One thought on “Excel VBA ComboBox Match Required

  1. Princess

    Hi Dinesh,

    I just wanna ask. Is it possible to transfer the specific excel cell to another excell template and automatically saved in PDF. Hiw can i do it using macro? Hope you answer my question.

    Thanks

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *