Welcome to Excel Avon
Searchable Dropdown in Userform
DOWNLOAD USED EXCEL FILE FROM HERE>>
In today’s post, we will show you How to make Searchable Dropdown in UserForm using Excel VBA, in this post we will work with Module and Userform as we created with Module in previous post. We have told the use of Userform in many posts, you have to create a Userform where we can search the value of the data.
To understand you, we will take some data so that we can explain to you, I am using the data of countries. The intent is to create an excel searchable dropdown list with a search suggestion mechanism such that when I type in the search bar it shows a dropdown with matching options.
Our searchable drop down in userform will be ready like this as you can see.
How to make Searchable Dropdown in Userform
As we taught you how to insert a userform when we were working with Userforms, as we have learned in many posts so far, you can insert Userforms. Let us teach you how we insert the userform. Let’s understand, then we have to go like last time, first go to the Developer Tab, then click on the option of Visual Basic as shown in the image below.
On opening in VBE, you have to go to Insert and then UserForm has to be inserted, as can be seen in the image.
When we click on the User form, a separate wizard box will be created in a window VBE, in which you can see UserForm1 as the default name. You can increase or decrease the size of the UserForm with the mouse pointer.
We’ll select the Userform Toolbox label and then drag it onto the Userform. By going to the properties of the label, there will be a label caption. Change the caption of the label ‘Search Name’. For making drop down we have to make label so that our drop down will look good.
Now we will drag the textbox to the userform by going to the toolbox. We will use this text box to fill the name to find the search value.
Now we will drag the Listbox to the userform by going to the toolbox. We will get that data in the listbox which has come in the search, so we will use the listbox.
We’ll right click in textbox and then click on view code then we will do coding for text box.
Here first of all declare variable as i as long and lr and long.
Private Sub TextBox1_Change() Dim i As Long, lr As Long End Sub
Here we will declare the last row of the sheet
Private Sub TextBox1_Change()
Dim i As Long, lr As Long
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
End Sub
Then we will create a condition if the value of textbox is blank then the listbox should also be clear as well as the list should not be visible
Private Sub TextBox1_Change()
Dim i As Long, lr As Long
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
If Me.TextBox1.Value = "" Then
Me.ListBox1.Clear
Me.ListBox1.Visible = False
Else
End If
End Sub
else list box will clear and also we are using a for loop.
Private Sub TextBox1_Change()
Dim i As Long, lr As Long
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
If Me.TextBox1.Value = "" Then
Me.ListBox1.Clear
Me.ListBox1.Visible = False
Else
Me.ListBox1.Clear
For i = 2 To lr
End If
End Sub
We create another condition, if the value of the text box is same as the value of column a of sheet 1. If the value the matches then the sheet item will be added to the listbox. here we are using LIKE Operator, the LIKE operator allows a pattern of string to be matched against the complete string.
Private Sub TextBox1_Change()
Dim i As Long, lr As Long
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
If Me.TextBox1.Value = "" Then
Me.ListBox1.Clear
Me.ListBox1.Visible = False
Else
Me.ListBox1.Clear
For i = 2 To lr
If Sheet1.Range("A" & i).Value Like "*" & Me.TextBox1.Value & "*" Then
Me.ListBox1.AddItem Sheet1.Range("A" & i).Value
End If
Next i
End If
End Sub
We create another condition, If the number of lists in the list box is more than zero, List count will be after add item in listbox then Make the List box visible.
Private Sub TextBox1_Change()
Dim i As Long, lr As Long
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
If Me.TextBox1.Value = "" Then
Me.ListBox1.Clear
Me.ListBox1.Visible = False
Else
Me.ListBox1.Clear
For i = 2 To lr
If Sheet1.Range("A" & i).Value Like "*" & Me.TextBox1.Value & "*" Then
Me.ListBox1.AddItem Sheet1.Range("A" & i).Value
End If
Next i
If Me.ListBox1.ListCount > 0 Then
Me.ListBox1.Visible = True
Else
End If
End Sub
else will make the List box invisible.
Private Sub TextBox1_Change()
Dim i As Long, lr As Long
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
If Me.TextBox1.Value = "" Then
Me.ListBox1.Clear
Me.ListBox1.Visible = False
Else
Me.ListBox1.Clear
For i = 2 To lr
If Sheet1.Range("A" & i).Value Like "*" & Me.TextBox1.Value & "*" Then
Me.ListBox1.AddItem Sheet1.Range("A" & i).Value
End If
Next i
If Me.ListBox1.ListCount > 0 Then
Me.ListBox1.Visible = True
Else
Me.ListBox1.Visible = False
End If
End If
End Sub
Now we’ll run the code
Here the searchable dropdown Userform is created. We wrote the value in the text box, the data came in the List box below. Here we have selected all the values but the value is not visible in the text box. So Now go back to VBE.
We’ll right click in List Box and then click on view code then we will do coding for listbox.
Already subroutine is here, Create condition If the value of selected item of Listbox is zero or greater than zero then The value that we select from the list box will be filled in the value text box. we will make Listbox visibility False.
Private Sub ListBox1_Click() If Me.ListBox1.ListIndex >= 0 Then Me.TextBox1.Value = Me.ListBox1.List(Me.ListBox1.ListIndex) Me.ListBox1.Visible = False End If End Sub
Now we’ll run the code
Enter the search value, On selecting the value the value will be filled in the text box. then close userform.
Now we will go to Insert and then Module has to be inserted, as can be seen in the image.
Now we will write subroutine here to open Userform.
Sub ShowDropdown() End Sub
Now give the Command to show in userform.
Sub ShowDropdown() UserForm1.Show End Sub
Open the worksheet and go to insert tab drag a button for assign macro.
First, name the button and then, we’ll assign Macro. Select macro and click Ok.
Here we have named the button as show search form because this button will open the form. click the button.
Now we can see the Searchable dropdown in userform here and we will wrote value in textbox. We didn’t get the correct value because of case sensitivity. Close the Searchable Dropdown in userform.
Add Lcase function to ignore the case of text , Add Lcase function in text box value.
Private Sub TextBox1_Change()
Dim i As Long, lr As Long
lr = Sheet1.Range("A" & Rows.Count).End(xlUp).Row
If Me.TextBox1.Value = "" Then
Me.ListBox1.Clear
Me.ListBox1.Visible = False
Else
Me.ListBox1.Clear
For i = 2 To lr
If LCase(Sheet1.Range("A" & i).Value) Like "*" & LCase(Me.TextBox1.Value) & "*" Then
Me.ListBox1.AddItem Sheet1.Range("A" & i).Value
End If
Next i
If Me.ListBox1.ListCount > 0 Then
Me.ListBox1.Visible = True
Else
End If
End Sub
Click on Show Search Form Button
Enter the value to search in dropdown, Select the value and value will filled in Text.
Therefore, I hope that you have understood How to make Searchable Dropdown in Userform using Excel VBA, maybe if you do not understand some options, then you can comment us, which we will answer soon and for more information, you can follow us on Twitter, Instagram, LinkedIn and you can also follow on YouTube.
DOWNLOAD USED EXCEL FILE FROM HERE>>
You can also see well-explained video here about How to make Searchable Dropdown in Userform using Excel VBA
