Drop Down List - Add New Items

[sspatriots]

I'm using Debra Dalgleish's "Drop Down List - Add New Items" code in the link below to allow users of the worksheet the opportunity to add new items to their drop down list if they want to. I use a ComboBox in my Userform to chose from the existing drop down list. However, the Userform won't allow adding new items to the drop down list like the user can currently do when in the worksheet. How could I get the ComboBox of the Userform trigger Debra Dalgleish's code to allow my user to add a new item to the Drop Down list from within the Userform? Any ideas or suggestions would be greatly appreciated. This is the last thing I need to do for this project I'm trying to complete. The "cmdAddUpdatePOData_Click" code I have is in another thread. I can add it here if that helps. Thanks, SS

Excel Drop Down Lists - Add New Items

 

[NoSparks]

I'm sorry, but I cannot figure out where to try this line of code.

Initially, load the combobox1 drop down from UserForm_Initialize, something like

Private Sub UserForm_Initialize()
    Me.ComboBox1.List = Range("whatever the named range is").Value
End Sub

Then use combobox_beforeupdate to decide if the new value should be added to the range and if it is reload the list

 

[sspatriots]

I went back and removed the row source property in the property box for each combobox. Not sure if I did it correctly or not, but below is what I added to my "cmdFindPO_Click" code to populate all of my comboboxes and it seems to be working so far. This kind of mirrors the example in post #5. If this doesn't appear correct, please let me know. Thanks, SS

    ComboBox2.List = Worksheets("Drops").ListObjects("tblSupplier").ListColumns("Supplier").DataBodyRange.Value
    ComboBox4.List = Worksheets("Drops").ListObjects("tblBy").ListColumns("By").DataBodyRange.Value
    ComboBox5.List = Worksheets("Drops").ListObjects("tblShipto").ListColumns("Ship to").DataBodyRange.Value
    ComboBox6.List = Worksheets("Drops").ListObjects("tblDescription").ListColumns("Description").DataBodyRange.Value
    ComboBox9.List = Worksheets("Drops").ListObjects("tblCustomer").ListColumns("Customer").DataBodyRange.Value

 

[sspatriots]

Initially, load the combobox1 drop down from UserForm_Initialize, something like

Private Sub UserForm_Initialize()
    Me.ComboBox1.List = Range("whatever the named range is").Value
End Sub

Then use combobox_beforeupdate to decide if the new value should be added to the range and if it is reload the list

I went back and moved it to the UserForm Initialize event. Thanks, SS

 

[sspatriots]

I do have one more question regarding Debra Dagleish's "Drop Down List - Add New Items" code. How would I go about having the Message box include the name of the list that the new item is being added to, because I have 5 different list on that worksheet called "Drops"?

I found the following Function online but can't make it work with her code (linked in my original post):

Function TableHeader(cl As Range) As Variant
    Dim lst As ListObject
    Dim strHeading As String

    Set lst = cl.ListObject

    If Not lst Is Nothing Then
        TableHeader = lst.HeaderRowRange.Cells(1, cl.Column - lst.Range.Column + 1).Value
    Else
        TableHeader = ""
    End If
End Function

 

[NoSparks]

I found the following Function online but can't make it work with her code (linked in my original post):

I wouldn't even try.

When I first saw this thread, I set up a test file to play with.
A userform with a couple of text boxes and one combobox.
sheet name different from yours
table name different from yours
ListColumn header different from yours
And a dynamic named range for the databody of the single column table.

Here's the entirety of its code, perhaps you can glean something from it.

Option Explicit


Private Sub ComboBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
    'ignore if blank
    If Me.ComboBox1.Value = "" Then Exit Sub
    
    Dim ws As Worksheet
    Dim oLo As ListObject
    Dim oNewRow As ListRow
    Dim myRsp As Integer
    
    Set ws = Sheets("DropLists")
    Set oLo = ws.ListObjects("Table1")
        
    'see if the entered item already exists in the dynamic named range
    If Application.WorksheetFunction.CountIf(ws.Range("Employees"), ComboBox1.Text) Then
        'if yes
        Exit Sub
    Else
        'if not
        myRsp = MsgBox("Add " & Chr(34) & Me.ComboBox1.Value & Chr(34) & " to the drop down list?", _
                vbQuestion + vbYesNo + vbDefaultButton1, _
                "New Item -- not in drop down")
        If myRsp = vbYes Then
             'add item to table
             With oLo
                 Set oNewRow = .ListRows.Add
                 With oNewRow
                     .Range.Cells(1).Value = ComboBox1.Text
                 End With
             End With
         End If
    End If
    
    'sort the list here
    
    'repopulate drop down
    ComboBox1.List = Range("Employees").Value
    
End Sub


Private Sub UserForm_Initialize()
    Me.ComboBox1.List = Range("Employees").Value
End Sub

Temporarily, the file is here

 

[sspatriots]

Thank you, I'll give it a try.

 

[sspatriots]

I ended up declaring a new variable for the header of the table my original target value is in because the same column headings are used in that table. It seems be working now with the following changes. Posting in case it helps someone else with a similar issue.

Added the following variable declaration:

Dim My_HValue As String   'Added, SPS, 10/04/22

Added just above the MsgBox:

My_HValue = Target.Offset(2 - Target.Row).Value 'Added, SPS, 10/04/22

Changed this:

   myRsp = MsgBox("Add this item to the drop down list?", _
      vbQuestion + vbYesNo + vbDefaultButton1, _
      "New Item -- not in drop down")

To this:

        myRsp = MsgBox("Add '" & My_Value & "' to the '" & My_HValue & "' drop down list?", _
            vbQuestion + vbYesNo + vbDefaultButton1, _
            "New Item -- not in drop down")