Welcome to Excel Avon
Add Data Validation drop down list Using VBA
DOWNLOAD USED EXCEL FILE FROM HERE>>
Today’s article is about Add Data Validation drop down list using Excel VBA, today we are going to understand how to Add Data Validation drop down list using Excel VBA, VBA Excel’s FIND function searches for a given set of values in a specified range. A drop-down list means that one cell includes several values. When the user clicks the arrow on the right, a certain scroll appears. He can choose a specific one.
A drop-down list is a very handy Excel tool for checking the entered data. The following features of drop-down lists allow you to increase the convenience of data handling: data substitution, displaying data from another sheet or file, the presence of the search and dependency function.
We have created some cells to add drop down list
How to Add Data Validation drop down list
Above you can see the cell in which we will add data validation drop down list. So, we have to go like last time, first go to the Developer Tab, then we will click on the Visual Basic option as shown in the image below.
On opening in VBE, you have to go to Insert and then Module has to be inserted, as can be seen in the image.
Once we insert the module, we will write a subroutine as Add List Class.
Sub AddListClass() End Sub
And then Define variable for Worksheet, set worksheet as Sheet 1.
Sub AddListClass() Dim WS As Worksheet Set WS = Sheet1 End Sub
Now we will add range of cells then use delete with validation function and by this we will clear the cell
Sub AddListClass()
Dim WS As Worksheet
Set WS = Sheet1
WS.Range("C4"). Validation. Delete
End Sub
After this again add cell range and use validation function with add and then we will use parameter type xlDVtype then next parameter is optional, but we will use formula parameter, In the formula parameter, we will add the data that will be stored in the dropdown list, as we have added all the data of the class in a separate range, we will add the range to the formula parameter.
Sub AddListClass()
Dim WS As Worksheet
Set WS = Sheet1
WS.Range("C4").Validation.Delete
WS.Range("C4"). Validation. Add xlValidateList, , ,"='" & WS.Name & "'!" & WS.Range("B12:B18").Address
End Sub
After writing the code, we will run the code
Then we will see in the worksheet, then we have added the Dropdown list and we can select any option here.
Dropdown list for Gender cell
Now we’ll go back to the VBE to create the code for the second cell ‘Gender’. Now we will copy the code that we created for the class in VBE and then paste it below, but we will have to change the name of the subroutine and then change the range and change the data written in the formula parameter,
Here we have to add formula parameter for ‘gender’ so we can write direct male and female.
We have bolded the place where we are changing the range, in the formula parameter and then the name of the subroutine.
Sub AddListClass_1()
Dim WS As Worksheet
Set WS = Sheet1
WS.Range("C6").Validation.Delete
WS.Range("C6").Validation.Add xlValidateList, , , "Male,Female"
End Sub
After this we will go click on the run button
Now we will go to the worksheet, then we can see that the dropdown list has been added in the cell, on this we can select the gender.
Dropdown List for Married? cell
Now we’ll go back to the VBE to create the code for the second cell ‘Married’. Now we will copy the code that we created for the class in VBE and then paste it below, but again we have to change the name of the subroutine and then change the range and the data written in the formula parameter, here we have to add formula parameter for ‘Married’ so we can write direct Yes and No.
We have bolded the place where we are changing the range, in the formula parameter and then the name of the subroutine.
Sub AddListClass_2()
Dim WS As Worksheet
Set WS = Sheet1
WS.Range("C8").Validation.Delete
WS.Range("C8").Validation.Add xlValidateList, , , "Yes, No"
End Sub
After this we will go click on the run button
Now we will go to the worksheet, then we can see that the dropdown list has been added in the cell, on this we can select the Married, yes or no .
So, I hope you have understood How to add Data Validation drop down list using Excel VBA and for more information, you can follow us on Twitter, Instagram, LinkedIn, and YouTube as well.
DOWNLOAD USED EXCEL FILE FROM HERE>>
You can also see well-explained video here about How to add Data Validation drop down list using Excel VBA
