Home > Validation > Drop Downs > Macros Drop Down List - Add New Items 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.

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.

Note: For Excel 2003 instructions, click here.

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