Maruf Islam
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.
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.
🔼 Using Worksheet Tab
Go to any worksheet, Right-Click on it > Choose View Code (from the Context Menu).
🔄 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.
🔼 You can also do it by Selecting Insert (from the Toolbar) > then Choosing Module.
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.
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 
➤ 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.
➤ 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.
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.
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).
Step 2: The Name Manager window opens. 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.
➤ Clicking OK assigns the range (C column) to the Name entity. Click on Close.
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
➤ 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.
6 – VBA 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.
➤ 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.
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.
