How to Create Dynamic Drop Down List Using VBA in Excel

In general, Excel worksheets contain numerous rows and columns. To assign an entry to different column values is quite annoying. However, a dynamic drop down list in Excel using VBA eases the findings.

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

 In this article, we demonstrate different ways of creating a dynamic drop down list in Excel using VBA.


Download Excel Workbook


⧭ Opening Microsoft Visual Basic and Inserting Code in the Module

Before proceeding to demonstrate any methods, it’s necessary to know the ways to open and insert a Module in Microsoft Visual Basic in Excel.

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

🔼 Using Keyboard Shortcuts

Press ALT+F11 altogether to open Microsoft Visual Basic window.

🔼 Using Developer Tab

In an Excel worksheet, Go to Developer Tab > Select Visual Basic. The Microsoft Visual Basic window appears.

Developer tab-Dynamic Drop Down List in Excel using VBA

🔼 Using Worksheet Tab

Go to any worksheet, Right-Click on it > 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,

🔼 After opening the Microsoft Visual Basic window, Select a Worksheet > Right-Click on it > Select Insert (from the Context Menu) > then Choose Module.

select worksheet-Dynamic Drop Down List in Excel using VBA

 🔼 You can also do it by Selecting Insert (from the Toolbar) > then Choosing Module.

module insertion-Dynamic Drop Down List in Excel using VBA


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

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

From the dataset (shown below), 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). In order to do that we must data validate the entire column and then use the entries to display as a dynamic drop down list in column B.

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

Step 1: 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
.Delete
.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 – assign 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.

Step 2: 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 

➤ In case you want to check whether the drop down list is dynamic or not. Add another name (i.e., Jenny Anderson) 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

Since the drop down list adds any afterward entered entries to the list, the drop down list is a dynamic list. For representation, we demonstrate the insertion of only one drop down list box in this method.

Read More: How to Create Dynamic Drop Down List Using Excel OFFSET (3 Ways)


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 want to take that method a step further. We create as many drop down list boxes as the data requires. And assign employees to their respective companies and addresses.

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

Step 1: Before commencing the procedure, Subject the Range (i.e., C:C) to a Name. Go to the Formulas tab > Click on Name Manager (from the Defined Names section).

Selecting Name manager

Step 2: The Name Manager window opens. Click on New.

Click on New

Step 3: In the New Name window, type a name in the Name dialog box. Paste the below formula in the Refers to command box.

Click on OK.

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

formula insertion

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

Name Manager window

Step 4: Double click on the Name sheet. The Name sheet’s macro window opens.

Then place 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
.Delete
.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.

Step 5: Afterward return to the worksheet. Click on the Drop Down List icon, you 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. To prove the drop down list as dynamic, 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. Therefore, the drop down list is a dynamic list.

dynamic drop down list

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


Conclusion

In this article, we’ve demonstrated two variants of macros to create a dynamic drop down list in Excel using VBA. Each macro we demonstrated has its own convenience depending on the data type. Hope these above-mentioned methods serve your purpose. Comment, if you have further inquiries or have anything to add.


Related Articles

Maruf Islam

Maruf Islam

My self, Maruf Islam, an engineer and Excel & VBA Content developer on Exceldemy. I enjoy solving problems, finding workable solutions, and most of the part I really like to take on challenges. On Exceldemy I write articles discussing various way outs of Microsoft Excel's stuck ons.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo