Add New Items to Data Validation Combo Box

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 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.

To see the file, and how it works, you can download it from my Contextures website. On the Excel Samples page, look for DV0062 - Data Validation Click Combobox - Add New Items. The file is in xls format, and contains macros.


5 comments to Add New Items to Data Validation Combo Box

  • Dave Roberts

    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 and it just has one file in it.

  • Dave Roberts

    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.

  • Don Leverton

    Hi Debra,

    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
    'open the drop down list automatically

    End If

    Application.ScreenUpdating = True
    Application.EnableEvents = True
    Exit Sub
    Resume exitHandler

    End Sub

Leave a Reply




You can use these HTML tags

<a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <s> <strike> <strong>