Search Contextures Sites

Excel Data Validation - Add New Items

 

Learn how to create Excel dashboards.

 

Add New Items to Data Validation Drop Down
Set Up the Excel Workbook
Create the Dynamic Named Ranges
Set Up the Data Entry Sheet
Add the Sort List Code
Test the Sort List Code
Add the Data Entry Code
Test the Data Entry Code
How the Data Entry Code Works
Watch the Excel Video Tutorial

To view the steps in a short video, click here:
Excel Data Validation Add Items Video

Download the zipped sample Excel Data Validation Add New Items file

Add New Items to Data Validation Drop Down

In this Excel data validation example, you'll create an Excel Data Validation drop down list that allows users to add new items.

The new data validation items will be automatically added to the drop down list, and the list will be sorted alphabetically.

data validation add item

Set Up the Excel Workbook

In the sample Excel 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.

pivot table top 10

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.

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)

pivot table top 10

.

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:

  1. Select the cells where you want the Client drop down lists
  2. On the Ribbon, click the Data tab, then click Data Validation (In Excel 2003, click the Data menu, then click 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 to allow invalid entries.
  6. Click OK

    data validation source

Repeat the steps for the Fruit data entry cells, using =FruitList as the source.

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 Excel VBA code, follow these steps:

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

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.

worksheet object

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.

procedure drop down

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

Watch the Excel Video Tutorial

To view the steps in a short video, click here:
Excel Data Validation Add Items Video

Download the zipped sample Excel Data Validation Add New Items file

Excel Tutorials - Data Validation

Data Validation Basics
Data Validation - Create Dependent Lists
Data Validation - Dependent Dropdowns from a Sorted List
Data Validation - Dependent Lists With INDEX
Hide Previously Used Items in a Dropdown List
Data Validation - Display Messages to the User
Data Validation - Display Input Messages in a Text Box
Data Validation - Use a List from Another Workbook
Data Validation Criteria Examples
Data Validation Custom Criteria Examples
Data Validation Tips
Data Validation Documentation
Data Validation Combo Box
Data Validation Combo Box - Named Ranges
Data Validation Combo Box -- Click
Data Validation - Add New Items

Learn how to create Excel dashboards.

 

Privacy Policy

 

Contextures Inc., Copyright ©2013
All rights reserved.

 

Last updated: May 19, 2013