Excel Drop Down List - Add New Items

See how to create a Data Validation drop down list that allows users to add new items, using a non-table list.

To create lists in a named Excel table, see the instructions here.

Add New Items to Drop Down

In this example, people who enter data on the spreadsheet can select from a data validation drop down list. If the item they want to enter is not in the list already, they can type the item in the cell.

This technique uses the Microsoft Excel data validation feature.

For any new items:

• macro automatically adds that item to the source list for the drop down
• items in the source list will be sorted alphabetically
• new items will automatically appear in the drop down list, the next time it is used

Video: Add New Items to Drop Down

Create a Data Validation drop down list that allows users to add new items. New data validation items will be automatically added to the drop down list, and the list is sorted alphabetically. The written instructions are below the video.

Set Up the Workbook

In the sample workbook, there's a data entry sheet, named DataEntry. It has two data entry columns, with the headings Client and Fruit, and data validation drop down lists in those columns.

On another worksheet, named Lists, there are the source lists for the data validation drop down lists. The lists are in separate columns, with a blank column in between.

The lists do not have a heading.

Create the Dynamic Named Ranges

After you type the lists of items for the data validation drop down lists, create a dynamic named range for each list, using the Excel OFFSET function.

The formulas that were used for the two dynamic named ranges in the sample file were:

• ClientList: =OFFSET(Lists!$D$1,0,0,COUNTA(Lists!$D:$D),1)
• FruitList: =OFFSET(Lists!$B$1,0,0,COUNTA(Lists!$B:$B),1)

Set Up the Data Entry Sheet

In the DataEntry sheet, follow these instructions to add data validation drop down lists in the columns for Client and Fruit:

• Select the cells where you want the Client drop down lists
• On the Ribbon, click the Data tab, then click Data Validation
• In the Data Validation dialog box, go to the Settings tab
• Click in the Allow box
• From the Allow drop-down list, select List
• In the Source box, type an equal sign and the list name, for example: =ClientList

Turn Off Error Alerts

The next step is important - you'll turn off the error alerts, so people can enter items that are not in the list as valid entries. Excel will not show an error message for invalid data in these cells.

• Next, go to the Error Alert tab
• Remove the check mark for this option: Show error alert after invalid data is entered
• Finally, click the OK button, to complete the data validation settings

Repeat the Steps

After you complete the changes to the Client data entry cells, repeat the steps for the Fruit data entry cells.

However, instead of using the ClientList as the source for the dropdown list, use this named range in the Source box formula:

• =FruitList

Add the Sort List Code

On the worksheet code module for the Lists sheet, you'll add code that runs automatically if a change is made on the worksheet.

To add the VBA code, follow these steps:

• Right-click the Lists sheet tab, and click View Code

The Visual Basic Editor (VBE) window opens, and the empty code module for the Lists sheet is shown.

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.

At the top right of the code window, click the arrow in the Procedure drop down, and click on Change.

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)
    Columns(Target.Column).Sort _
        Key1:=Cells(1, Target.Column), _
        Order1:=xlAscending, _
        Header:=xlNo, _
        OrderCustom:=1, _
        MatchCase:=False, _
        Orientation:=xlTopToBottom
End Sub

This code figures out which column the change was made in (Target.Column), and then sorts that column.

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.

Add the Data Entry Code

On the worksheet code module for the DataEntry sheet, you'll add 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 add the VBA code, follow these steps:

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
Dim ws As Worksheet
Dim str As String
Dim i As Integer
Dim rngDV As Range
Dim rng As Range

If Target.Count > 1 Then Exit Sub
Set ws = Worksheets("Lists")
  
If Target.Row > 1 Then
  On Error Resume Next
  Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
  On Error GoTo 0
  If rngDV Is Nothing Then Exit Sub
  
  If Intersect(Target, rngDV) Is Nothing Then Exit Sub
    
  str = Target.Validation.Formula1
  str = Right(str, Len(str) - 1)
  On Error Resume Next
  Set rng = ws.Range(str)
  On Error GoTo 0
  If rng Is Nothing Then Exit Sub
  
  If Application.WorksheetFunction _
    .CountIf(rng, Target.Value) Then
    Exit Sub
  Else
    i = ws.Cells(Rows.Count, rng.Column).End(xlUp).Row + 1
    ws.Cells(i, rng.Column).Value = Target.Value
    rng.Sort Key1:=ws.Cells(1, rng.Column), _
      Order1:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom
  End If

End If

End Sub

This code 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 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. If so, the macro stops running:

If Target.Count > 1 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
'more code
End If

Then, the code tries to set a range based on the data validation cells in the worksheet. If there are no data validation cells, the range can't be set, so the macro stops running:

  On Error Resume Next
  Set rngDV = Cells.SpecialCells(xlCellTypeAllValidation)
  On Error GoTo 0
  If rngDV Is Nothing Then Exit Sub

Next, the code checks to see if the cell that was changed (Target) intersects with the range of cells that contain data validation. If it doesn't, the macro stops running:

  If Intersect(Target, rngDV) Is Nothing 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 on the Lists sheet, the range can't be set, so the macro stops running:

  On Error Resume Next
  Set rng = ws.Range(str)
  On Error GoTo 0
  If rng Is Nothing Then Exit Sub

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, the code finds the first empty row in the list's column on the Lists sheet:

    i = ws.Cells(Rows.Count, rng.Column).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 sorts the revised list on the Lists sheet:

    rng.Sort Key1:=ws.Cells(1, rng.Column), _
      Order1:=xlAscending, Header:=xlNo, _
      OrderCustom:=1, MatchCase:=False, _
      Orientation:=xlTopToBottom

Get the Sample File

Get the zipped sample Data Validation Add New Items file

More Data Validation Tutorials

Data Validation Basics Excel 2003

Create Dependent Drop Down Lists 

Use a Data Validation List from Another Workbook

Data Validation Criteria Examples

Data Validation Custom Criteria Examples

Data Validation Tips