How to remove item from combobox in a userform?

Question

I have a list of names in a sheet. I set these names as my RowSource for a combobox on a useform.

There are two Comboboxes involved. One starts full, the other starts empty.

I want when I click on a name from the first (full) combobox, said name to be added to the other combobox, and removed from the original combobox (and vice versa eventually).

I can't remove anything with RemoveItem.

I went the 'Menu.ListeAjoutAg.ListIndex' way to get my current selection's index ('Menu' is the UserForm and 'ListeAjoutAg' is the combobox), but it did not work.
Tried inputting through a variable I created real quick, 'b', but same result. No index number works. I checked and I only feed the function integers (0, 1, 3, 4...) that are correct and/or well within the scope of my list (about 45 names).

Private Sub ListeAjoutAg_Change()
    a = Menu.ListeAjoutAg.Text
    b = Menu.ListeAjoutAg.ListIndex
    Menu.ListeRetirer.AddItem (a) ' goes fine till there
    Menu.ListeAjoutAg.RemoveItem (b) 'and here it goes wrong
    Menu.ListeRetirer.Enabled = True
    Menu.ListeRetirer.Visible = True
End Sub

If you set a Rowsource for a listbox, you neither can add nor remove items. You will need to fill the listbox manually (simply loop over all cells of the rowsource and use `AddItem`) — FunThomas, Nov 16 '22 at 17:28

score 1 · Accepted Answer · answered Nov 16 '22 at 17:48

As already mentioned: You can't add or remove items from a Listbox if you have set the Rowsource property.

However, it is rather easy to fill a Listbox from a range - simply copy the cell values into an array and assign the array as List. See for example VBA Excel Populate ListBox with multiple columns

Put the following routine into your form and call it from the form Activate event.

Private Sub fillListBox(r As Range)
    Me.ListeAjoutAg.Clear
    Me.ListeAjoutAg.ColumnCount = r.Columns.Count
    
    Dim data
    data = r.Value
    Me.ListeAjoutAg.List = data
End Sub

Private Sub UserForm_Activate()
    Dim r As Range
    ' Replace this with the range where your data is stored.
    Set r = ThisWorkbook.Sheets(1).Range("A2:C10") 
    fillListBox r
End Sub

oh... I think I finally understand what happens. Well first of all I managed to remove things according to what I wanted thanks to filling the combo the way you advised, and now it still throws an error, the same, but that's because removing the selected item "changes" the combobox and thus it goes back into the function, this time without anything selected so the index is -1... I'll jus catch the error and ignore it I guess. Thanks a lot! — ThismaddePro, Nov 16 '22 at 22:03

How to remove item from combobox in a userform?

1 Answers1