Home > Validation > Drop Downs > Add New Items Excel Drop Down List - Add New ItemsSee how to create a Data Validation drop down list that allows users to add new items, using a list on the worksheet. For example, type Cherry in the Fruit selection column, then go to the next row. When you open the drop down list, Cherry was automatically added to the options, in alphabetical order. Note: If you want to create lists in a named Excel table, see the instructions here. |
Create the Dynamic Named RangesAfter 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:
|
Set Up the Data Entry SheetIn the DataEntry sheet, follow these instructions to add data validation drop down lists in the columns for Client and Fruit:
Turn Off Error AlertsThe 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.
Repeat the StepsAfter 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:
|
Add the Data Entry CodeOn 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. |
Test the Data Entry CodeTo see how the sort code works, you can add an item to one of the lists: |
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 FileGet the zipped sample Data Validation Add New Items file More Data Validation TutorialsData Validation Basics Excel 2003 Create Dependent Drop Down Lists Use a Data Validation List from Another Workbook Data Validation Criteria Examples |
Last updated: July 8, 2023 12:11 PM