Excel drop-down list with data from another sheet / file When you enter a new name in the empty cell of the drop-down list, the following message will appear: «Add entered name Baobab?».Ĭlick «OK» and one more row with the «Baobab» value will be added. Save it, setting the «Excel Macro-Enabled Workbook» file type.Private Sub Worksheet_Change( ByVal Target As Range) Dim lReply As Long If > 1 Then Exit Sub If Target.Address = "$C$2" Then If IsEmpty(Target) Then Exit Sub If WorksheetFunction.CountIf(Range( "trees"), Target) = 0 Then lReply = MsgBox( "Add entered name " & _ Target & " in the drop-down list?", vbYesNo + vbQuestion) If lReply = vbYes Then Range( "trees").Cells(Range( "trees").Rows.Count + 1, 1) = Target End If End If End If End Sub
#How to make a drop down list in excel 2011 code
Copy the code (just insert your parameters). Alternatively, press Alt + F11 simultaneously. To do this, right-click on the name of the sheet and go to the «View Code» tab. If you do not do this, Excel will not allow you to enter new values.
That is, you need to select a table style with a header row. In our example, the header is cell A1 with the word «Trees». For solving our task, design does not matter. Find the «Format As Table» tool in the main menu. Highlight the range for the drop-down list.
If changes are made to the available range (data are added or deleted), they are automatically reflected in the drop-down list. It is necessary to make a drop-down list with values from the dynamic range.