Excel dependent drop down list VBA
Author: Oscar Cronquist Article last updated on May 06, 2019 Show This article demonstrates how to automatically create drop-down lists if adjacent data grows, there are two methods explained here. The first method uses an Excel defined Table. The benefit of using a drop-down list in an Excel defined Table is that the whole table column contains drop-down lists automatically, it will also expand automatically if more values are added to the Table meaning you don't need to copy and paste drop-down lists. You only need to copy a drop-down list and paste once to a cell in the Table column and Excel will instantly fill the remaining column cells with the same drop-down list. The second method is an event procedure that makes sure there are drop-down lists in column A if there are adjacent values in column B and C. Table of Contents Add Drop Down lists automaticallyCreate a table
Name Excel defined Table
Repeat the steps above with cell range E1:E8, name the Excel defined Table: Table2 Apply data validations lists to the Table
The entire first column now contains a drop-down list in each cell. If the table expands, the new cell has a drop-down list! This article demonstrates how to use excel defined tables in a drop-down list: How to use an Excel Table name in Data Validation Lists and Conditional Formatting formulas Back to top Applying data validation lists dynamically (VBA)The data validation lists contain values from a named range. The named range (E2:E8) expands when you add new values to the list. The animated gif shows you that. The animated gif below also shows you when adding a new company name in cell B11, a drop-down list (Data validation list) is instantly applied to cell range A2:A11. The VBA code in this sheet and a named formula make it all happen! Let me explain how I created this sheet, instead of using an Excel defined Table I created a named range that expands. I have created comments to the VBA code I created, you can find it further down in this article. Dynamic named range
Add VBA code to sheet
VBA code'Event code that runs if a cell value changes. 'The Target argument contains the cell address that changed. Private Sub Worksheet_Change(ByVal Target As Range) 'Dimension variables and declare data types Dim Lrow As Single Dim AStr As String Dim Value As Variant 'Check if Target cell is located in column B or C or if a cell value changed in column E. If Not Intersect(Target, Range("$B:$C")) Is Nothing _ Or Not Intersect(Target, Range("E:E")) Is Nothing Then 'Save last non-empty cell row in column B to variable Lrow Lrow = ActiveSheet.Range("B" & Rows.Count).End(xlUp).Row 'Add values in named range Region to string variable AStr For Each Value In Range("Region") AStr = AStr & "," & Value Next Value 'Populate a cell range in column A with drop-down lists, based on the number of rows in variable Lrow 'The drop-down lists contain values from string variable AStr With ActiveSheet.Range("A2:A" & Lrow).Validation .Delete .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= _ xlBetween, Formula1:=AStr .IgnoreBlank = True .InCellDropdown = True .InputTitle = "" .ErrorTitle = "" .InputMessage = "" .ErrorMessage = "" .ShowInput = True .ShowError = True End With End If End SubBack to top
A drop-down list means that one cell includes several values. When the user clicks the arrow on the right, a certain scroll appears. He can choose a specific one. A drop-down list is a very handy Excel tool for checking the entered data. The following features of drop-down lists allow you to increase the convenience of data handling: data substitution, displaying data from another sheet or file, the presence of the search and dependency function. Path: the «DATA» menu – the «Data Validation» tool – the «Settings» tab. The data type – «List». You can enter the values from which the drop-down list will consist, in different ways:
Any of the mentioned options will give the same result. It is necessary to make a drop-down list with values from the dynamic range. If changes are made to the available range (data are added or deleted), they are automatically reflected in the drop-down list.
Let’s test it. Here is our table with a list on one sheet: Add the new value «Spruce» to the table. Now delete the «Birch» value. The "smart table", which easily "expands" and changes, has helped us to perform our task. Now let's make it possible to enter new values directly into the cell with this list and have data automatically added to the range.
When you enter a new name in the empty cell of the drop-down list, the following message will appear: «Add entered name Baobab?». Click «OK» and one more row with the «Baobab» value will be added. Excel drop-down list with data from another sheet / fileWhen the values for the drop-down list are located on another sheet or in another workbook, the standard method does not work. You can solve the problem with the help of the =INDIRECT() function: it will form the correct link to an external source of information.
The name of the file from which the information for the list is taken is enclosed in square brackets. This file must be opened. If the book with the desired values is stored in a different folder, you need to specify the path completely. How to create dependent drop-down listsTake three named ranges: It is an indispensable prerequisite. Above you can see how to turn a normal scroll in a named range (using the «Name Manager»). Remember that the name cannot contain spaces or punctuation.
Selecting multiple values from a drop-down list in ExcelSometimes, you need to select several items from the drop-down list. Let's consider the ways of performing this task.
Do not forget to change the ranges to "your own" ones. Create scroll in the classical way. The rest of the work will be done by macros. Searchable drop-down list in Excel
Download drop-down lists example When you enter the first letters from the keyboard, the appropriate items are displayed. These are not all the pleasant moments of this instrument. Here you can customize the visual representation of information, specify two columns at once as a source. |