VBA excel - adding combo box item to list box

Question

guys I hope someone can help me with this one. I have a combo box that has data from a named range and I would like to select a value from the combo box and add it to the list box.

Currently I can add an item into the list box with a button but once I add another it overwrites the current item.

Also It needs to be able to add an item at the bottom if the list box already has some values in it.

I think it has something to do with finding the last row but I'm not sure, any help would be highly appreciated :)

image of the issue

Dim i As Integer

With Me.lb_lease

.ColumnCount = 3
.ColumnWidths = "200;50;50"
.AddItem
.List(i, 0) = cbox_hardware.Column(0)
.List(i, 1) = cbox_hardware.Column(1)
.List(i, 2) = cbox_hardware.Column(2)
i = i + 1

End With

Also, I think this will solve your issue [Link](https://stackoverflow.com/a/6973396/3042759) — Tom, Nov 21 '17 at 09:50
It's the code for a button, I only used the loop just searching through various code posted on here so a bit unsure sorry — Sean McQuarrie, Nov 21 '17 at 09:52
The `i` is the row number in the `ListBox`. I suspect it is looking at the wrong row. As you are adding an item without an index number it will be added to the end of the `ListBox`. Try replacing `.List(i,` with `.List(.ListCount-1,` (as detailed in that link). This will target the last row in the `ListBox` (i.e. the one you just added) — Tom, Nov 21 '17 at 09:54

score 0 · Answer 1 · answered Nov 21 '17 at 10:26

I suggest to separate the actions of setting up the listbox and adding items to it. The procedure below will set up the box and clear all existing content. Change the names of the worksheet and the Listbox to match your circumstances. The code will also work if the listbox is in a userform.

Private Sub ResetListBox()

    With Worksheets("LibraryAccount").ListBox1
        .ColumnCount = 3
        .ColumnWidths = "80;50;50"
        .Clear                          ' delete current list
    End With
End Sub

The next procedure adds an item to it. It requires a semi-colon separated string, like "One;Two;Three". You might concatenate it from your combobox result using ListIndex to identify the row. The procedure will disassemble the string and add it at the bottom of the list. Worksheet and ListBox names must be changed.

Private Sub AddToListBox(AddArray As String)

    Dim Arr() As String
    Dim i As Integer
    Dim C As Long

    Arr = Split(AddArray, ";")

    With Worksheets("LibraryAccount").ListBox1
        i = .ListCount
        .AddItem
        For C = 0 To 2
            .List(i, C) = Arr(C)
        Next C
    End With
End Sub

The procedure below is for testing the above procedure. You can run ResetListbox and then call TestAdd several times.

Private Sub TestAdd()
    AddToListBox "One;Two;Three"
End Sub

VBA excel - adding combo box item to list box

1 Answers1