How to Create Dynamic Drop Down List Using VBA in Excel

Let’s say we have a dataset where Employee Name, Company Name, and Business Addresses are displayed. We want a dynamic drop-down list where we can assign any employee name besides the Company Name and Business Addresses.

Dataset-Dynamic Drop Down List in Excel using VBA

Opening Microsoft Visual Basic and Inserting Code in the Module

There are mainly 3 ways to open Microsoft Visual Basic window:

  • Using Keyboard Shortcuts: Press Alt + F11 to open the Microsoft Visual Basic window.
  • Using Developer Tab: In an Excel worksheet, go to Developer Tab and select Visual Basic.

Developer tab-Dynamic Drop Down List in Excel using VBA

  • Using Worksheet Tab: Go to any worksheet, right-click on it, and choose View Code from the Context Menu.

worksheet option-Dynamic Drop Down List in Excel using VBA

Inserting a Module in Microsoft Visual Basic: There are 2 ways to insert a Module in Microsoft Visual Basic window:

  • Select a Worksheet and Right-Click on it, then select Insert (from the Context Menu) and choose Module.

select worksheet-Dynamic Drop Down List in Excel using VBA

  •  Select Insert from the Toolbar and choose Module.

module insertion-Dynamic Drop Down List in Excel using VBA

Dynamic Drop-Down List in Excel Using VBA: 2 Easy Ways to Create

Method 1 – Range to Create a Dynamic Drop-Down List in Excel

From the dataset, we know that our worksheet contains multiple employee names. We want to create a dynamic drop-down list using the range (i.e., C column).

method-1 dataset-Dynamic Drop Down List in Excel using VBA


  • Open Microsoft Visual Basic then insert a Module using the instruction section.
  • Paste the following macro in any Module:
Sub Dynmc_DropDown_Range()
Dim wrk As Worksheet: Set wrk = Worksheets(2)
Dim nameText As String
Dim nameRng As Range
nameText = "DynamicList"
Set nameRng = wrk.Range("$C:C")
ThisWorkbook.Names.Add Name:=nameText, RefersTo:=nameRng
With Worksheets(2).Cells(3, "B").Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, _
Formula1:="=" & nameRng.Address
End With
End Sub

method-1 macro

 ➤ in the code,

1 – start the macro procedure by declaring the Sub name. You can assign any name to the code.

2 – declare the variable then assign the variable to create a DynamicList in the Name Manager. Also assign the range (i.e., $C:C).

3 – assigns the names and range to the Worksheet2.

4 – data validation starts from row 3 in column B using the WITH statement and displays the nameRng (i.e., column C) entries in the validation.

  • Press F5 to run the macro.
  • After returning to the workbook, click on the drop-down icon.
  • We see all the entries from column C are present in the drop down list as depicted below picture.

drop down list 

  • Add another name (i.e., Jenny Anderson) to the column, then click on the drop-down icon in column B. You find out the last entry is added automatically to the list.

dynamic drop down list

Read More: How to Make a Dynamic Data Validation List Using VBA in Excel

Method 2 – Dynamic Drop-Down List Using Name Manager

In Method 1, we created only one drop-down dynamic list box. In this method, we’ll create as many drop-down list boxes as the data requires. Our dataset is organized as shown in the below image. We want column F’s Name entries in column B (i.e., Employee Name) to assign the Company and Addresses.

method-2 dataset-Dynamic Drop Down List in Excel using VBA


  • Select the Range (i.e., C:C) to give it a Name.
  • Go to the Formulas tab and click on Name Manager (from the Defined Names section).

Selecting Name manager

  • The Name Manager window opens. Click on New.

Click on New

  • In the New Name window, type a name in the Name dialog box.
  • Paste the below formula in the Refers to command box:
=OFFSET(Name!$F$2, 0, 0, COUNTA(Name!$F:$F)-1)

The OFFSET function takes Name!$F$2 as the reference, 0 and 0 as rows and columns. In the end, COUNTA (Name!$F:$F)-1 portion as the range height. 1 is subtracted from the count to ignore the column heading.

  • Click on OK.

formula insertion

  • Clicking OK assigns the range (C column) to the Name entity. Click on Close.

Name Manager window

  • Double-click on the Name sheet. The Name sheet’s macro window opens.
  • Paste the following code there:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim mrfrow As Single
Dim nameText As String
Dim Value As Variant
If Not Intersect(Target, Range("$C:$D")) Is Nothing _
Or Not Intersect(Target, Range("F:F")) Is Nothing Then
mrfrow = ActiveSheet.Range("C" & Rows.Count).End(xlUp).Row
For Each Value In Range("Name")
nameText = nameText & "," & Value
Next Value
With ActiveSheet.Range("B2:B" & mrfrow).Validation
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _
xlBetween, Formula1:=nameText
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
End If
End Sub

method-2 macro

➤ From the above image, in the sections,

1 – begin the macro code declaring the VBA Macro Code’s Sub name.

2 – declare the variables as Single and String and Variant.

3 – impose a condition on targeted ranges using the VBA IF statement. Also, check whether the ranges intersect or not using the VBA Intersect Method.

4 – assign mrfrow to a range (i.e., column C) variable in case of no intersection.

5 – for each value in the range the macro assigns the nameText variable a formula.

6VBA With Statement applies data validation in column B ignoring blanks, error messages, etc.

  • Return to the worksheet.
  • Click on the Drop-Down List icon, where you can see all the names are available to get inserted in column B.

drop down list

  • You can assign all the employee names according to the Name column (i.e., F column) in column B. You can also type a new name, company, and business address in the respective rows, then click on the drop-down list icon.
  • After scrolling you see the newly entered name present in the drop-down list.

dynamic drop down list

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

Download Excel Workbook

Related Articles

Get FREE Advanced Excel Exercises with Solutions!
Maruf Islam
Maruf Islam

MARUF ISLAM is an excellent marine engineer who loves working with Excel and diving into VBA programming. For him, programming is like a superhero tool that saves time when dealing with data, files, and the internet. His skills go beyond the basics, including ABACUS, AutoCAD, Rhinoceros, Maxsurf, and Hydromax. He got his B.Sc in Naval Architecture & Marine Engineering from BUET, and now he's switched gears, working as a content developer. In this role, he creates techy content... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF