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

 

[Max1616]

Hello! I would add a button to your userform, and then run this code when the button is pressed:

Private Sub CommandButton1_Click() 'If you change the name of your button in the user form (properties pane), also change it here.
Dim NewItem As String

NewItem = InputBox("Please choose a new value to add to the dropdown") 'You can edit the prompt to whatever you'd like

Me.ComboBox1.AddItem NewItem 'Change "Combobox1" to the name of your combobox in the userform

End Sub

 

[NoSparks]

use ComboBox_BeforeUpdate

 

[sspatriots]

use ComboBox_BeforeUpdate

That is what I tried, but got a Method of "Activate method of 'Range' class failed" error in my code code below:

Error on this line:

            POnum.Offset(, 1).Activate

Private Sub ComboBox2_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)

    Dim ws As Worksheet
    Dim tb As ListObject
    Dim PO_No As String, POnum As Range

    Set ws = ThisWorkbook.Sheets("2022")
    Set tb = ws.ListObjects("Table46")

    PO_No = Trim(TextBox1.Text)
    
    With tb.Range.Columns(4)

        Set POnum = .Find(What:=PO_No, after:=.Cells(1), LookIn:=xlValues, _
                          SearchOrder:=xlByRows, searchdirection:=xlNext)

        If Not POnum Is Nothing Then

            TextBox1 = POnum

            POnum.Offset(, 1) = ComboBox2.Text
            
            POnum.Offset(, 1).Activate

        End If

    End With

End Sub

 

[mikerickson]

How is your ComboBox getting its value? If you are using the .RowSource property, then the .AddItem method won't work.
Better than .RowSource would be to use the .listproperty to fill the list.

ComboBox1.List = Application.Transpose(Range("A1:A20").Value)

 

[NoSparks]

I suspect Sheets("2022") is not the active sheet

The Debra Dalgleish thing adds a new item to the bottom of a named range.
Doesn't appear to be what you're trying to accomplish.

 

[sspatriots]

I suspect Sheets("2022") is not the active sheet

The Debra Dalgleish thing adds a new item to the bottom of a named range.
Doesn't appear to be what you're trying to accomplish.

I am in the process of updating all of my code to call the sheets the Active Sheets as well as reference the table as the only table on each worksheet (below is what I'm using for that).

    Dim ws As Worksheet
    Dim tb As ListObject
    Dim PO_No As String, POnum As Range
    
    Set ws = ThisWorkbook.ActiveSheet 'Sheets("2022")
'    assumes Table is the first one on the ActiveSheet
    Set tb = ActiveSheet.ListObjects(1)
'    Set tb = ws.ListObjects("Table46")

I actually do want to accommodate the user by letting them add items to the list with a message box that ask them first if they want to add the item to the list. It works on the worksheet drop-downs, but not on the UserForm, which is what her code does.

Below is what my "cmdAddUpdatePOData_Click" code looks like right now.

Private Sub cmdAddUpdatePOData_Click()

    Dim ws As Worksheet
    Dim tb As ListObject
    Dim PO_No As String, POnum As Range
    
    Set ws = ThisWorkbook.ActiveSheet 'Sheets("2022")
'    assumes Table is the first one on the ActiveSheet
    Set tb = ActiveSheet.ListObjects(1)
'    Set tb = ws.ListObjects("Table46")

'PO_No = Trim(TextBox1.Text)
PO_No = Split(Trim(TextBox1.Text) & " ", " ")(0)    'Added per MrExcel message board, SPS, 09/29/22

    With Application
        .DisplayAlerts = False                'Turns off alerts
    '    .AlertBeforeOverwriting = False       'Turns off overwrite alerts
        .ScreenUpdating = False               'Turns off screen updating
    End With
   
    ShowHideLogSheet
    
    With tb.Range.Columns(4)
    
        Set POnum = .Find(What:=PO_No, after:=.Cells(1), LookIn:=xlValues, _
                          SearchOrder:=xlByRows, searchdirection:=xlNext)           '& "*" took out because code is adding a row now
                         
        If Not POnum Is Nothing Then
            
            POnum = TextBox1.Text
                        
            POnum.Offset(, 1) = ComboBox2.Text
            POnum.Offset(, 2) = TextBox3.Text
            POnum.Offset(, 3) = ComboBox4.Text
            POnum.Offset(, 4) = ComboBox5.Text
            POnum.Offset(, 5) = ComboBox6.Text
            POnum.Offset(, 6) = TextBox7.Text
            POnum.Offset(, 7) = TextBox8.Text
            POnum.Offset(, 8) = ComboBox9.Text
            POnum.Offset(, 9) = TextBox10.Text
            POnum.Offset(, 10) = TextBox11.Text
            POnum.Offset(, 11) = TextBox12.Text

        Else
  
            If TextBox1.Text = "" Then
        
                cmdFindPO_Click
    
            Else
  
            MsgBox "Sorry, your PO_No was not found"
            
            End If
            
        End If
    
    End With

TextBox1.Text = Format(TextBox1.Text, "0000")

Repaint

    With Application
        .DisplayAlerts = True                'Turns on alerts
    '    .AlertBeforeOverwriting = False       'Turns off overwrite alerts
        .ScreenUpdating = True               'Turns on screen updating
    End With

End Sub

 

[NoSparks]

Any reason for not answering post #5 ?

Knowing how, when and from where the combobox1 drop down is being populated is key to the solution you need.

 

[sspatriots]

Any reason for not answering post #5 ?

Knowing how, when and from where the combobox1 drop down is being populated is key to the solution you need.

I'm trying to make that work right now. I did have a row source property in the properties box for that combobox.

 

[sspatriots]

How is your ComboBox getting its value? If you are using the .RowSource property, then the .AddItem method won't work.
Better than .RowSource would be to use the .listproperty to fill the list.

ComboBox1.List = Application.Transpose(Range("A1:A20").Value)

I'm sorry, but I cannot figure out where to try this line of code. The drop down table that I reference is on a sheet called "Drops" and in a named range called "tblSupplier".