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

Published by

Dinesh Kumar Takyar

Welcome to exceltrainingvideos.com! My aim is to help you learn MS Excel including VBA. I always felt that a good way to learn something was to find solutions to problems in that domain. That is why I share these Excel videos with you. Mostly these are questions asked during my corporate training sessions. I've been training individuals and companies in computers since 1991. I did my B. Sc. (Vordiplom), M. Sc. (Diplom) and Ph.D. (Dr. rer. nat.) from Hamburg, Germany. The best thing about solving some of my visitor's questions is that I also gain new insights. For more Excel VBA solutions you may like to visit my YouTube channel: http://youtube.com/familycomputerclub For a structured Excel VBA training course online you can visit: https://www.youtube.com/excelvbaonline

One thought on “Excel VBA ComboBox Match Required”

  1. 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

Leave a Reply

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