A.N.M. Mohaimen Shanto
In Microsoft Excel, a data validation list is one of those tools that allows you to validate your data in the worksheet. It actually helps you save a lot of time selecting a particular range of values. If your cell only takes specific values, you don’t have to type again and again. Instead, you can create a drop down list for data validation in your Excel worksheet. In this tutorial, you will learn exactly how to create your first Data Validation List From an Array with Excel VBA.
This tutorial will be on point with suitable examples and proper illustrations. So, read the entire article to enrich your knowledge.
Download Practice Workbook
Download this practice workbook.
What Is Data Validation in Excel?
Now, data validation allows you to control your input in a cell. When you have limited values to enter a field, you can use the drop down lists to validate your data. You don’t have to enter data by typing again and again. The data validation list also ensures that your inputs are error-free.
Now, why it is called data validation? Because it makes sure only the valid data make the list.
It is basically helpful for those users who are introduced for the first time to the dataset. They don’t have to manually input the data. Instead, they can choose any values from the drop down or data validation list that you’ve created.
Step-by-step Procedure to Create Data Validation List from an Array with Excel VBA
First of all, have a look at our dataset:
Here, we have a dataset of some salespersons’. We have their working region and selling products. Here, we are going to create a data validation list for the Region and Product columns.
Our data validation list will contain:
- Region: “North”, “South”, “East”, “West”
- Product: “TV”, “Fridge”, “Mobile”, “Laptop”, “AC”
You can create a validation list in the traditional way. But, here we will use the VBA code. Now, in the VBA code, we will put them into an array. And from that array, we will validate our data.
In the following section, I will build the code in a step-by-step manner. I recommend you to build your code along with me. In this way, you will learn the code better. Let’s get into it.
Build the VBA Code to Create an Excel Data Validation List from Array
In this section, you will learn to build your data validation list from an array using the VBA codes in Excel. Here, our Region and Product column will contain a drop down list.
📌 Step 1: Open VBA Editor
- First, press Alt+F11 on your keyboard to open the VBA editor.
- Then, select Insert>Module.
📌 Step 2: Declare Sub-procedure
Now, type the following code:
Sub data_validation_from_array()
End SubThis is our subprocedure. We will type all the codes inside this.
📌 Step 3: Declare Necessary Variables
Now it’s time to declare necessary variables that we will have to use further.
Sub data_validation_from_array()
Dim region, product As Variant
Dim region_range, product_range As Range
End SubWe are declaring our arrays as Variant. In this variable, we will have some strings.
region_range, product_range: These variables will store the range of our columns Region and Product
📌 Step 4: Set the Arrays
Sub data_validation_from_array()
Dim region, product As Variant
Dim region_range, product_range As Range
region = Array("North", "South", "East", "West")
product = Array("TV", "Fridge", "Mobile", "Laptop", "AC")
End SubAs you can see, we have stored some strings in the region and product variable. We will use them to create our drop down list.
Read More: How to Split a String into an Array in VBA (3 Ways)
📌 Step 5: Set the Data Validation Range
Sub data_validation_from_array()
Dim region, product As Variant
Dim region_range, product_range As Range
region = Array("North", "South", "East", "West")
product = Array("TV", "Fridge", "Mobile", "Laptop", "AC")
Set region_range = Range("C5:C10")
Set product_range = Range("D5:D10")
End SubSet region_range = Range(“C5:C10”): By this line of code, we are indicating the Region column.
Set product_range = Range(“D5:D10”): And this line of code is specifying the Product column.
Read More: How to Convert Range to Array in Excel VBA (3 Ways)
📌 Step 6: Create Data Validation List in Region Column
Sub data_validation_from_array()
Dim region, product As Variant
Dim region_range, product_range As Range
region = Array("North", "South", "East", "West")
product = Array("TV", "Fridge", "Mobile", "Laptop", "AC")
Set region_range = Range("C5:C10")
Set product_range = Range("D5:D10")
With region_range.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(region, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = "Please Provide a Valid Input"
.ShowInput = True
.ShowError = True
End With
End SubWith region_range.Validation: With this line we select the column of Region.
.Delete: If there is any pre existing validation list, it will delete those.
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(region, “,”): We are adding a data validation list here.
- AlertStyle determines what type of alert we are going to show if the user gives an entry outside the list.
- Formula1:=Join(region, “,”): By the Formula, we are providing values in the validation list. We had some strings in the region By using the Join method, we are combining them with a separator comma(,). These values or items will be our source in the validation list.
.IgnoreBlank = True: By this line, we are permitting blank values.
.InCellDropdown = True: We will display a drop down list with acceptable values.
.ErrorTitle = “Error”: We are setting title of the data-validation error dialog box.
.ErrorMessage = “Please Provide a Valid Input”: It will set an error message in the data validation error dialog box
.ShowInput = True: It will display the data validation input message whenever the user clicks on a cell in the data validation range.
.ShowError = True: It will show the error dialog box if the user gives any invalid input.
Read More: VBA to Get Unique Values from Column into Array in Excel (3 Criteria)
Similar Readings
- VBA to Transpose Array in Excel (3 Methods)
- How to Name a Table Array in Excel (With Easy Steps)
- Excel VBA to Read CSV File into Array (4 Ideal Examples)
- Excel VBA: How to Filter with Multiple Criteria in Array (7 Ways)
📌 Step 7: Create Data Validation List in Product Column
Sub data_validation_from_array()
Dim region, product As Variant
Dim region_range, product_range As Range
region = Array("North", "South", "East", "West")
product = Array("TV", "Fridge", "Mobile", "Laptop", "AC")
Set region_range = Range("C5:C10")
Set product_range = Range("D5:D10")
With region_range.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(region, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = "Please Provide a Valid Input"
.ShowInput = True
.ShowError = True
End With
With product_range.Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(product, ",")
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = "Error"
.InputMessage = ""
.ErrorMessage = "Please Provide a Valid Input"
.ShowInput = True
.ShowError = True
End With
End SubWith product_range.Validation: With this line we select the column of Product.
.Delete: If there is any pre existing validation list, it will delete those.
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Formula1:=Join(product, “,”): We are adding a data validation list here.
- AlertStyle determines what type of alert we are going to show if the user gives an entry outside the list.
- Formula1:=Join(region, “,”): By the Formula, we are providing values in the validation list. We had some strings in the product By using the Join method, we are combining them with a separator comma(,). These values or items will be our source in the validation list.
.IgnoreBlank = True: By this line, we are permitting blank values.
.InCellDropdown = True: We will display a drop down list with acceptable values.
.ErrorTitle = “Error”: We are setting title of the data-validation error dialog box.
.ErrorMessage = “Please Provide a Valid Input”: It will set an error message in the data validation error dialog box
.ShowInput = True: It will display the data validation input message whenever the user clicks on a cell in the data validation range.
.ShowError = True: It will show the error dialog box if the user gives any invalid input.
Run the VBA Code
We have built our VBA code already. Now, it’s time to check whether the code is working properly or not. We will run this code in our current sheet.
First, press Alt+F8 on your keyboard to open the Macro dialog box.
Next, select data_validation_from_array and click on Run.
Now, click on any cell in the Region column.
Here you can see the drop down icon beside the cell. Now, click on the drop down icon.
Here, you can see all the values in the validation list. We gave this value in our region array. Now, select the data for each cell.
Let’s check the Product column. Click on any cell in the Product column.
We also got a drop down icon here. Now, click on the drop down.
As you can see, all the values that we gave in the product array in our VBA code are shown here. So, we successfully used the VBA codes in Excel to create a validation list from an array.
Now, let’s give a value that is not in our given array. We are trying with the product “Headphone”.
Now, press Enter. After that you will see the following:
As you can see, it is displaying an error dialog box. We already set the error title and error message in our VBA code and it is showing exactly that.
Read More: Create Data Validation Drop-Down List with Multiple Selection in Excel
💬 Things to Remember
✎ You can copy any cell with data validation and paste it to other cells. The resulting cells will have the same data validation list.
✎ This is not a dynamic array. If you want to expand your data validation list, just add them as a string in the arrays. It will do fine.
Conclusion
To conclude, I hope this tutorial has provided you with a piece of useful knowledge to create a data validation list in Excel using VBA codes. We recommend you learn and apply all these instructions to your dataset. Download the practice workbook and try these yourself. Also, feel free to give feedback in the comment section. Your valuable feedback keeps us motivated to create tutorials like this.
Don’t forget to check our website Exceldemy.com for various Excel-related problems and solutions.
Keep learning new methods and keep growing!
Related Articles
- How to Use IF Statement in Data Validation Formula in Excel (6 Ways)
- Use Named Range for Data Validation List with VBA in Excel
- How to ReDim Preserve 2D Array in Excel VBA (2 Easy Ways)
- Excel VBA: Remove Duplicates from an Array (2 Examples)
- Calculate the Average of an Array with VBA (Macro, UDF, and UserForm)
