Combo box

[matts12]

I am using a combo box in a user form to add items from a populated list to a table. If I am getting a new item that I don't have in my dropdown list, what line of code could I use to make the new item appear in my combo box drop down list? I don't have any sample vba code

 

[Micron]

All depends on what your list (combo row source property) is based on. Since you're already adding items to a table with this combo, the combo must have a list source of some kind. Anyone would need to know how that was done in order to suggest how to add items.

 

[matts12]

All depends on what your list (combo row source property) is based on. Since you're already adding items to a table with this combo, the combo must have a list source of some kind. Anyone would need to know how that was done in order to suggest how to a

 

[matts12]

I have a vba code in the combobox to add items to a table.

Private sub Userform_Activate

With Me.ComboBox1
.AddItem"Apples"
.AddItem"Banannas"
.AddItem"Oranges"

End With
End Sub

 

[matts12]

I had someone suggest me this code, but have no idea how to add that. I keep getting errors.

If(IsBlank(Combobox.Selected.Value), Combobox.SearchText, Combobox.Selected.Value)

 

[Micron]

Then you'd have to use code to add items. Pretty sure IsBlank is a worksheet function to test a cell so it won't work in code to test a form control. How about this, which would fire if I type in "fish" and try to move out of the combo (change combobox1 to your combo name). Your list would need to have at least 1 item in it already.

Private Sub ComboBox1_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
Dim i As Integer, result As Integer
Dim bolExists As Boolean

For i = 0 To ComboBox1.ListCount - 1
   If ComboBox1.List(i) = ComboBox1 Then bolExists = True
Next

If Not bolExists Then
   result = MsgBox("Entered value is not in the list. Do you wish to add it?", vbYesNo)
   If result = vbYes Then
      Me.ComboBox1.AddItem ComboBox1
   Else
      Cancel = True
      Me.ComboBox1 = ""
   End If
End If

End Sub

 

[matts12]

Thank you very much. That's exactly what I'm looking for. However the item doesnt stay in the list after you close the form. Is there a way that it could be added to the first code that I posted

 

[Micron]

Good point. In Access, this would be simple as the list values can be entered and retained in the property sheet. It's another thing lacking about Excel that makes me shake my head - unless there's something about that feature which I haven't figured out. If you can save and add to your list in a sheet somewhere, that should greatly simplify this. AFAIK, without a list (range) you have to run code to set the combo values as you show in post 4. Unfortunately I know of no way to modify that code on the fly (as in modifying it to include the new items as code lines). So short of editing that code every time, I know of no way to retain list items without using a range.

 

[matts12]

Ok so if I use the very last column in a sheet as a range to add items, how would you write it then?

 

[Mario_R]

Hello all. Let's see if I explain myself clearly:

a) You have a listObject called 'Table1' which contains your list: Apples, Bananas and Oranges.
b) These data are incorporated into your comboBox1 by:

Private Sub UserForm_Initialize()
  ComboBox1.RowSource = "Table1"
End Sub

(AddItem no longer has to use it.)

c) Add the following VBA code to your UserForm:

Private Sub ComboBox1_Exit(ByVal Cancel As MSForms.ReturnBoolean)
Dim tbl As ListObject
If ComboBox1.ListIndex = -1 And ComboBox1 <> "" Then
  If MsgBox("Entered value is not in the list. Do you wish to add it?", vbYesNo) = vbYes Then
    Set tbl = Range(ComboBox1.RowSource).ListObject
    ComboBox1.RowSource = ""
      tbl.ListRows.Add.Range = ComboBox1
      With tbl.Range
        .Sort .Cells(1), 1, , , , , , xlYes
      End With
    ComboBox1.RowSource = tbl.Name
  End If
End If
End Sub

That is to say: when you write an element that is not in the list of the comboBox1, when leaving the comboBox1 the code asks you if you want to incorporate that data into the listObject.

If you answer 'Yes' then the data is added to the listObject 'Table1'.