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.
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:
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.
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.
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:
In the DataEntry sheet, follow these instructions to add data validation drop down lists in the columns for Client and Fruit:
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.
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:
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:
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.
To see how the sort code works, you can make a change to one of the lists:
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:
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.
To see how the sort code works, you can add an item to one of the lists:
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 zipped sample Data Validation Add New Items file
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
Last updated: March 23, 2022 3:52 PM