Excel vba create drop down list
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. Show
You can download the sample file, and this tutorial explains how it is set up, in the Ribbon versions of Excel (2013, 2010, 2007). For Excel 2003 instructions, click here. Add New Items to Drop Down ListIn 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 DownCreate 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 SetupIn 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. Dynamic Named RangesBecause 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.
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 SetupIn 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:
Repeat the steps for the Fruit column (cell C2), using =FruitList as the source. View the Sort List CodeOn 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 SubThis 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 CodeTo see how the sort code works, you can make a change to one of the lists:
View the Data Entry CodeOn 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:
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 CodeTo see how the sort code works, you can add an item to one of the lists:
How the Data Entry Code WorksFirst, 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 SubNext, 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 IfThen, 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 SubThen, 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 SubIf the range is set, the ws variable is set to that named range's parent worksheet.: Set ws = rng.ParentThen, 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 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 + 1The changed cell's value is added in that empty row on the Lists sheet: ws.Cells(i, rng.Column).Value = Target.ValueFinally, 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 WithGet the Sample FileGet the zipped sample Add New Items to Drop Down Lists file More Data Validation TutorialsData Validation Basics Create Dependent Lists Data Validation Custom Criteria Examples Data Validation Tips Data Validation Combo Box Last updated: July 10, 2021 7:33 PM |