The salespersons’ dataset has their working region and selling products. We will create a data validation list for the Region and Product columns.
Our data validation list will contain the following:
- Region: “North”, “South”, “East”, “West”
- Product: “TV,” “Fridge,” “Mobile,” “Laptop,” “AC”
Build the VBA Code to Create an Excel Data Validation List from Array
Step 1: Open VBA Editor
- Press Alt+F11 to open the VBA editor.
- Select Insert>Module.
Step 2: Declare Sub-procedure
- Enter 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
- We will have to use it further to declare the necessary variables.
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 SubWe 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 SubSet region_range = Range(“C5:C10”): By this line of code, we indicate the Region column.
Set product_range = Range(“D5:D10”): This line of code specifies the Product column.
Read More: How to Use Named Range for Data Validation List with VBA in Excel
Step 6: Create a Data Validation List in the 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 will show if the user gives an entry outside the list.
- Formula1:=Join(region, “,”): We provide values in the validation list by the Formula. We had some strings in the region. By using the Join method, we combined them with a separator comma(,). These values or items will be our source in the validation list.
.IgnoreBlank = True: By this line, we permit blank values.
.InCellDropdown = True: We will display a drop-down list with acceptable values.
.ErrorTitle = “Error”: We are setting the 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 invalid input.
Step 7: Create a Data Validation List in the 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 will show if the user makes an entry outside the list.
- Formula1:=Join(region, “,”): we provide values in the validation list by the Formula. We had some strings in the product. By using the Join method, we combined them with a separator comma(,). These values or items will be our source in the validation list.
.IgnoreBlank = True: By this line, we permit blank values.
.InCellDropdown = True: We will display a drop-down list with acceptable values.
.ErrorTitle = “Error”: We are setting the 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: The error dialog box will show if the user gives invalid input.
Run the VBA Code
We have already built our VBA code. Now, it’s time to check whether the code is working properly. We will run this code in our current sheet.
- Press Alt+F8 to open the Macro dialog box.
- Select data_validation_from_array and click on Run.
- Click on any cell in the Region column.
Here, you can see the drop-down icon beside the cell.
- 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.
- Select the data for each cell.
Let’s check the Product column.
- Click on any cell in the Product column.
We also have a drop-down icon here.
- 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.
- Let’s give a value that is not in our given array. We are trying the product “Headphone”.
- Press Enter. You will see the following:
As you can see, it is displaying an error dialog box. We already set the error title and message in our VBA code, which shows exactly that.
Read More: VBA to Select Value from Drop Down List in Excel
Things to Remember
✎ 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, add them as a string in the arrays. It will do fine.
Download the Practice Workbook
Download this workbook to practice.
Related Articles
- Unique Values in a Drop Down List with VBA in Excel
- How to Create Dynamic Drop Down List Using VBA in Excel
- How to Make a Dynamic Data Validation List Using VBA in Excel
- How to Make Multiple Dependent Drop Down List with Excel VBA
