Excel VBA to Create Data Validation List from Array

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.

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 Sub

This 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 Sub

We 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 Sub

As you can see, we have stored some strings in the region and product variable. We will use them to create our drop down list using VBA.

📌 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 Sub

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

📌 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 Sub

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

📌 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 Sub

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

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

Download Practice Workbook

Download this practice workbook.

Data Validation List from an Array.xlsm

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.

Keep learning new methods and keep growing!

Get FREE Advanced Excel Exercises with Solutions!