VBA - Can't remove item from combobox in a userform-CodePudding

Here's the thing. I have a list of names in a sheet. I set these names as my RowSource for a combobox on a useform. There are 2 Combobox (not UserForms my bad) involved. One starts full, the other starts empty. What I want to do is simple: when I click on a name from the first (full) combobox, I want said name to be added to the other combobox, and removed from the original combox. (and vice versa eventually but I ain't there yet). The thing is, I can't for the life of me 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

Any idea what the hell went wrong?

CodePudding user response：

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