There are a few versions of the data validation combo box technique on my website. The files use programming to make a combo box appear when you click, or double-click, on a cell that has a drop down list. The latest version shows how to add new items to data validation combo box lists.
Combo Box Benefits
The advantage to using a combo box is that you can show the text in a larger font, and it autocompletes as you type, unlike a cell with a drop down list.
Add New Item to Drop Down List
Another one of my sample files let you add new items to a drop down list, on the fly. If you type a new item, a message box asks if you want to add it. Then, if you click Yes, it’s put into the list, in alphabetical order.
Add New Items to Combo Box
Someone recently asked if it was possible to combine those techniques, so I’ve created a new sample file that does just that! When you click on a cell that has a data validation list, a combo box appears. If you type an item that isn’t in the list, you’re prompted to add it.
NOTE: The Add New Item code is triggered when you press Tab or Enter to get out of the Combo box.
Get the Excel File
To see the file, and take a peek at how the file and its code work, you can download the file from my Contextures website. On the Excel Samples page, in the Data Validation section, look for DV0062 – Data Validation Click Combobox – Add New Items.
The zipped file is in xls format, and contains macros.
___________________
I downloaded DV0062. Extracted, it’s two files: DataValDynamic.xls and DataValDynamic.xlsx with neither of them containing macros.
The .xlsx file has the same Data Entry tab (Fruit, Other Stuff, Vegetables) as the .xls file but the Lists worksheet in the .xlsx file shows Stages, Epic Names, Mermain Names, etc versus fruit and stuff.
@Dave, that’s strange! I don’t have any sample files with Epic Names, that I know of.
The zipped file at DV0062 is named DataValCombobox_AddSort_Multi.zip and it just has one file in it.
How embarrassing! (Insert very red face here.) I confused the new folder with another folder I had downloaded from your site two years ago. I found the correct folder/file. Sorry.
@Dave, no problem! Thanks for letting me know that you found the file, and I’m just glad that embarrassing stuff like that never happens to me. 😉
Hi Debra,
http://www.contextures.com/excelworksheetcomboboxes.html#listfill
I’m glad I found your notes on Excel 2013 … as it was driving me nuts!
Just one (very small) change to your code to append the “B” (i.e. “=SignListB” alias of “=SignList”) and it’s working perfectly now!
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim str As String
Dim cboTemp As OLEObject
Dim ws As Worksheet
Dim Tgt As Range
Set Tgt = Target.Cells(1, 1)
Set ws = ActiveSheet
On Error GoTo errHandler
Set cboTemp = ws.OLEObjects(“TempCombo”)
On Error Resume Next
If cboTemp.Visible = True Then
With cboTemp
.Top = 10
.Left = 10
.Width = 10
.ListFillRange = “”
.LinkedCell = “”
.Visible = False
.Value = “”
End With
End If
On Error GoTo errHandler
If Target.Validation.Type = 3 Then
‘if the cell contains a data validation list
Application.EnableEvents = False
‘get the data validation formula
str = Target.Validation.Formula1 & “B”
‘Had to create a Name “alias” because Excel 2013 won’t use a direct table ref
str = Right(str, Len(str) – 1)
‘Debug.Print str
With cboTemp
‘show the combobox with the list
.Visible = True
.Top = Target.Top
.Left = Target.Left
.Width = Target.Width + 15
.Height = Target.Height + 5
.ListFillRange = str
.LinkedCell = Target.Address
End With
cboTemp.Activate
‘open the drop down list automatically
Me.TempCombo.DropDown
End If
exitHandler:
Application.ScreenUpdating = True
Application.EnableEvents = True
Exit Sub
errHandler:
Resume exitHandler
End Sub