Excel vba create drop down list

Usually, drop down lists are set up to block invalid entries, using data validation. However, with a bit of programming, you can allow new items to be added to the drop down lists in your worksheet.

You can download the sample file, and this tutorial explains how it is set up, in the Ribbon versions of Excel [2013, 2010, 2007]. For Excel 2003 instructions, click here.

Add New Items to Drop Down List

In this tutorial, you'll see how the sample file is set up, with drop down lists that allow users to add new items. If a new item is entered, a message appears, asking if you would like to add that item to the drop down list.

If you click Yes, the new item is added to the drop down list, in alphabetical order.

Video: Add New Items to Drop Down

Create a drop down list that allows users to add new items. If you confirm, new items will be automatically added to the drop down list, alphabetically. The written instructions are below the video.

Workbook Setup

In the sample file, there's a data entry sheet, named DataEntry. There is a formatted table, with the headings Client and Fruit, and drop down lists in each column.

On another worksheet, named Lists, there are tables with the items for the drop down lists. The lists are in separate tables, with a blank column in between. The tables are named -- tblClients and tblFruit.

Dynamic Named Ranges

Because the lists are in formatted tables, it's easy to create a dynamic named range for each list, which will grow or shrink as items are added or removed.

1. Select the data in one of the lists -- do not include the heading cell.
2. Click in the Name Box, to the left of the Formula Bar, and type a one word name, e.g. ClientList
3. Press Enter, to complete the name
4. Repeat these steps for the other list.

You can see the dynamic named ranges in the sample file -- on the Ribbon, click the Formulas tab, then click Name Manager. The addresses look static -- Lists!$B$2:$B$6 -- but they are dynamic because they are based on table data.

Data Entry Sheet Setup

In the DataEntry sheet, the Clients and Fruit columns have data validation drop down lists. which allow you to enter items that are not in the list. Here is how they were set up:

1. Select cell B2, where the Client drop down list will appear
2. On the Ribbon, click the Data tab, then click Data Validation.
3. From the Allow drop-down list, choose List
4. In the Source box, type an equal sign and the list name, for example: =ClientList

5. On the Error Alert tab, remove the check mark from "Show error alert after invalid data is entered".

6. Click OK

Repeat the steps for the Fruit column [cell C2], using =FruitList as the source.

View the Sort List Code

On the Lists sheet, you can add or remove items from the lists. These lists will also be updated if someone adds a new item from the DataEntry sheet.

On the worksheet code module for the Lists sheet, there is code that runs automatically if you make a change on the worksheet.

To see the Excel VBA code, right-click the Lists sheet tab, and click View Code. On the code module, you can see the Private Sub Worksheet_Change code:

Private Sub Worksheet_Change[ByVal Target As Range] Dim strList As String On Error Resume Next strList = Cells[1, Target.Column].ListObject.Name If strList "" Then Application.ScreenUpdating = False With ListObjects[strList].Sort .SortFields.Clear .SortFields.Add _ Key:=Cells[2, Target.Column], _ SortOn:=xlSortOnValues, _ Order:=xlAscending .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With With ListObjects[strList] .Resize .DataBodyRange.CurrentRegion End With End If Application.ScreenUpdating = True End Sub

This code figures out which column the change was made in [Target.Column]. If the change was in a formatted table [ListObject], it sorts that list, and resizes the table, if necessary.

Test the Sort List Code

To see how the sort code works, you can make a change to one of the lists:

• In the first blank cell at the end of the Fruit list, type Apricot, then press the Enter key. The Fruit list will be sorted automatically, so Apricot moves between Apple and Banana
• Click on the cell that contains Apricot, then press the Delete key. The Fruit list will be sorted automatically, so the blank cell moves to the end of the list.

View the Data Entry Code

On the worksheet code module for the DataEntry sheet, there is code that runs automatically if a change is made on the worksheet.

Note: For code that allows only one specific user to add new items, see Data Entry Code - Specific User

To view the VBA code, right-click the DataEntry sheet tab, and click View Code. On the code module, you can see the Private Sub Worksheet_Change code:

1. Right-click the DataEntry sheet tab, and click View Code. The Visual Basic Editor [VBE] window opens, and the empty code module for the DataEntry sheet is shown.
2. At the top left of the code window, click the arrow in the Object drop down, and click on Worksheet. A couple of lines of code will be automatically added to the code module, and you can ignore that code, or delete it.
3. At the top right of the code window, click the arrow in the Procedure drop down, and click on Change.
4. When the cursor is flashing, type or paste the following code, between the Private Sub Worksheet_Change and End Sub lines

Private Sub Worksheet_Change[ByVal Target As Range] On Error Resume Next On Error Resume Next Dim ws As Worksheet Dim str As String Dim i As Integer Dim rngDV As Range Dim rng As Range Dim lCol As Long Dim myRsp As Long Dim strList As String If Target.Count > 1 Or Target.Value = "" Then Exit Sub If Target.Row > 1 Then If Target.Validation.Type 3 Then Exit Sub str = Target.Validation.Formula1 str = Right[str, Len[str] - 1] Set rng = ThisWorkbook.Names[str].RefersToRange If rng Is Nothing Then Exit Sub Set ws = rng.Parent If Application.WorksheetFunction _ .CountIf[rng, Target.Value] Then Exit Sub Else myRsp = MsgBox["Add this item to the drop down list?", _ vbQuestion + vbYesNo + vbDefaultButton1, _ "New Item -- not in drop down"] If myRsp = vbYes Then lCol = rng.Column i = ws.Cells[Rows.Count, lCol].End[xlUp].Row + 1 ws.Cells[i, lCol].Value = Target.Value strList = ws.Cells[1, lCol].ListObject.Name With ws.ListObjects[strList].Sort .SortFields.Clear .SortFields.Add _ Key:=Cells[2, lCol], _ SortOn:=xlSortOnValues, _ Order:=xlAscending .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With With ws.ListObjects[strList] .Resize .DataBodyRange.CurrentRegion End With End If End If End If End Sub

This code asks if you want the new item in the drop down. If Yes, it adds new items to the data validation lists, and sorts the source item list if an item was added. There is a detailed explanation of the code below.

Test the Data Entry Code

To see how the sort code works, you can add an item to one of the lists:

• In the the Client column, type "Ann", then press the Enter key.
• Click Yes, to add the new item to the list.
• Click the drop down arrow in the Client column, and you'll see that Ann now appears in the drop down list.
• Check the Lists sheet, and you'll see that Ann was added to the ClientList range, between Al and Bea.

How the Data Entry Code Works

First, the code checks to see if more than one cell was changed, or if the cell was left empty. If so, the macro stops running:

If Target.Count > 1 Or Target.Value = "" Then Exit Sub

Next, the code checks to which row was changed. If it was row 1, where the headings are located, the macro stops running.:

If Target.Row > 1 Then ... End If

Then, the code checks the data validation type [ type 3 is a drop down list] in the target cell.:

If Target.Validation.Type 3 Then Exit Sub

Then, the code creates a text string, based on the data validation formula in the changed cell. The Right function removes the first character from the string. For example, if the data validation formula is =ClientList the str variable would be set to ClientList:

str = Target.Validation.Formula1 str = Right[str, Len[str] - 1]

Then, the code tries to set a range based on the str variable. If there is no range with that name, the range can't be set, so the macro stops running:

Set rng = ThisWorkbook.Names[str].RefersToRange If rng Is Nothing Then Exit Sub

If the range is set, the ws variable is set to that named range's parent worksheet.:

Set ws = rng.Parent

Then, the code checks that range, to see if the changed cell's value is already in that list. If so, the macro stops running:

If Application.WorksheetFunction _ .CountIf[rng, Target.Value] Then Exit Sub

If the changed cell contains a new item, a message appears, asking if you want to add the item to the drop down list:

myRsp = MsgBox["Add this item to the drop down list?", _ vbQuestion + vbYesNo + vbDefaultButton1, _ "New Item -- not in drop down"]

If you click Yes, the code finds the table's column, and first empty row in that column, on the Lists sheet:

If myRsp = vbYes Then lCol = rng.Column i = ws.Cells[Rows.Count, lCol].End[xlUp].Row + 1

The changed cell's value is added in that empty row on the Lists sheet:

ws.Cells[i, rng.Column].Value = Target.Value

Finally, the code gets the name of the table, and sorts the revised list on the Lists sheet, and resizes it, if necessary:

strList = ws.Cells[1, lCol].ListObject.Name With ws.ListObjects[strList].Sort .SortFields.Clear .SortFields.Add _ Key:=Cells[2, lCol], _ SortOn:=xlSortOnValues, _ Order:=xlAscending .Header = xlYes .MatchCase = False .Orientation = xlTopToBottom .SortMethod = xlPinYin .Apply End With With ws.ListObjects[strList] .Resize .DataBodyRange.CurrentRegion End With

Get the Sample File

Get the zipped sample Add New Items to Drop Down Lists file

More Data Validation Tutorials

Data Validation Basics

Create Dependent Lists

Data Validation Custom Criteria Examples

Data Validation Tips

Data Validation Combo Box

Last updated: July 10, 2021 7:33 PM