How to Use Named Range for Data Validation List with VBA in Excel

Last updated: February 16, 2023
Get FREE Advanced Excel Exercises with Solutions!

If you are looking for the easiest ways to use named range for data validation list in Excel VBA, then you will find this article useful. Named ranges are useful to use in a data validation formula to make a dropdown list easily and this task can be made super easy with the help of some VBA codes.
So, let’s start our main article to explore the ways of using named ranges in a data validation list.

Download Workbook


4 Ways to Use Named Range for Data Validation List in Excel with VBA

Here, we have the following dataset containing the records of some products and their respective salesperson’s lists. Using this dataset we will try to show different ways with different VBA codes to use named ranges in a data validation list.

Excel VBA data validation list named range

We have used Microsoft Excel 365 version here, you can use any other versions according to your convenience.


Method-1: Using Named Range in Data Validation for Making a Dropdown List

Here, we have named the range of the Fruits column with Fruits and using a VBA code we will create a dropdown list in cell D6.

Excel VBA data validation list named range

Step-01:
➤ Go to the Developer Tab >> Visual Basic Option.

using named range

Then, the Visual Basic Editor will open up.
➤ Go to the Insert Tab >> Module Option.

Excel VBA data validation list named range

After that, a Module will be created.

using named range

Step-02:
➤ Write the following code

Sub Datavalidation1()

Range("D6").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Formula1:="=Fruits"

End Sub

Here, Validation will be added to cell D6, xlValidateList is for creating a dropdown list and the formula is used as the name of the range “=Fruits”.

using named range

➤ Press F5 and later click on the dropdown symbol of cell D6.
Then, you will get the list of the fruits and select any one item from the list like the Cherries.

using named range

Finally, we are getting our selected item in cell D6.

Excel VBA data validation list named range

Read More: How to Make a Data Validation List from Table in Excel (3 Methods)


Method-2: Adding Named Range and a Data Validation List with a VBA Code

We will not create any named range here manually, rather a simple VBA code will form a named range, and then, using it we will get the dropdown list finally in cell D6.

Excel VBA data validation list named range

Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code

Sub Datavalidation2()

ActiveWorkbook.Names.Add Name:="Fruit", _
RefersTo:=ThisWorkbook.Worksheets("Add").Range("B4:B10")
Range("D6").Validation.Add Type:=xlValidateList, _
AlertStyle:=xlValidAlertStop, Formula1:="=Fruit"

End Sub

Firstly, it will add the name Fruit to the range “B4:B10” of the worksheet Add.
Then, we will add Validation to the cell D6, xlValidateList is for creating a dropdown list and the formula is used as the name of the range “=Fruit”.

adding named range

➤ Press F5, then, go to the worksheet and click on the dropdown symbol of cell D6.
Later, you will get the list of the fruits and select any one item from the list like the Blueberries.

adding named range

So, we have got our desired item Blueberries from the list and besides this, we can see our created named range for the fruits.

Excel VBA data validation list named range

Related Content: Create Data Validation Drop-Down List with Multiple Selection in Excel


Similar Readings:


Method-3: Updating Data Validation List with Named Range Using Excel VBA

Suppose, we have the following dropdown list in cell D6, which works fine for the fixed dataset.

Excel VBA data validation list named range

But, if we add an extra vegetable Lettuce then it will not appear in the dropdown list which means our dropdown list is not updated automatically in this case.

updating drop down list

To update the list quickly and automatically you can follow this method.


3.1: Creating the Updated Named Range

Firstly, we have to add a name for the range of Column B in a way that it will automatically take the newly added items into this name.
➤ Go to the Formulas Tab >> Defined Names Group >> Name Manager Option.

Excel VBA data validation list named range

Then, the Name Manager dialog box will open up.
➤ Click on the New option.

updating drop down list

After that, the Edit Name wizard will pop up.
➤ Write down Vegetables in the Name box and the following formula in the Refers to box and finally press OK.

=OFFSET(Update!$B$4, 0, 0, COUNTA(Update!$B:$B)-2)

Here, Update! Is the sheet name, $B$4 is the reference cell from which we want to move, 0 for Rows and Columns arguments means it will remain in its reference or starting position.
COUNTA will count the number of cells with any type of values in Column B and then 2 will be subtracted because of the heading of the dataset in B1 and the header of the column in B3. So, you will get only the number of cells containing any vegetables.
This number will be the return reference from the starting position and OFFSET will always return the updated named range here.

updating drop down list

Then, you will be taken to the Name Manager wizard.
➤ Press Close.

Excel VBA data validation list named range


3.2: Using a VBA Code to Apply Data Validation List

➤ Right-click on the sheet name and select the View Code option.

updating drop down list

Afterward, the code window will appear.

Excel VBA data validation list named range

➤ Type the following code

Sub worksheet_Change(ByVal newitem As Range)

Dim updatedrange, item
If Not Intersect(newitem, Range("B:B")) Is Nothing Then
For Each item In Range("Vegetables")
updatedrange = updatedrange & "," & item
Next item
With ActiveSheet.Range("D6").Validation
      .Delete
      .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
       xlBetween, Formula1:=updatedrange
      .IgnoreBlank = True
      .InCellDropdown = True
      .InputTitle = ""
      .ErrorTitle = ""
      .InputMessage = ""
      .ErrorMessage = ""
      .ShowInput = True
      .ShowError = True
End With
End If

End Sub

This code will execute only if any change of values or addition occurred and so we have defined the procedure as Worksheet_Change, Worksheet is the Object and Change is the Procedure.
The newitem contains the address of the cell in which we are adding the new values and we defined it as Range. The datatype of updatedrange and item would be considered as Variant, where we have assigned updatedrange to the updated named range of the vegetables and item is for the values of each cell of this range.
The FOR loop will assign the updated range to updatedrange and the WITH statement will avoid the repetition of the same object, and finally, we have added the validation.

updating drop down list

Now, it’s time to return to the main sheet and check out the effect after the addition of a product Lettuce.
As we can see, we have this new item now in our dropdown list.

updating drop down list

After selecting this new item we are having it in cell D6.

Excel VBA data validation list named range

Read More: How to Use Data Validation List from Another Sheet (6 Methods)


Method-4: Using Named Range for Creating a Conditional Drop Down List

Here, we will create a dropdown list in cell E6 which will be dependent on the condition of cell D6 and for this reason, we have the following two named ranges, such as fruit1 and vegetable1.

Excel VBA data validation list named range

Excel VBA data validation list named range

Steps:
➤ Follow Step-01 of Method-1.
➤ Write the following code

Sub Datavalidation4()

If Range("D6") = "Fruits" Then
Range("E6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=fruit1"
Else
Range("E6").Validation.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Formula1:="=vegetable1"
End If

End Sub

IF-THEN statement will check if the value in cell D6 is Fruits and for this value, as a list, we will get the named range fruit1 as a list in cell E6 otherwise we will get the named range vegetable1 as a list in cell E6.

Conditional dropdown list

➤ Press F5, then go to the worksheet and click on the dropdown symbol of cell E6.
After that, you will get the list of the fruits for the category as Fruits in cell D6 and select any one item from the list like the Blackberries.

Conditional dropdown list

So, we have got our desired item Blackberries from the list.

Conditional dropdown list

➤ For changing the category as Vegetables we can see that we are having the list of vegetables after running the code.

Excel VBA data validation list named range

After selecting Broccoli we are getting this item in cell E6.

Conditional dropdown list

Related Content: How to Use IF Statement in Data Validation Formula in Excel (6 Ways)


Practice Section

For doing practice by yourself we have provided a Practice section like below in a sheet named Practice. Please do it by yourself.

Practice


Conclusion

In this article, we tried to cover the ways to use a named range for the data validation list in Excel VBA easily. Hope you will find it useful. If you have any suggestions or questions, feel free to share them in the comment section.


Related Articles

Tanjima Hossain
Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo