Adding items from 2 cells to a multicolumn Listbox

[stirlingmw1]

Good Morning

I have a worksheet "Uplift" on which I have a Data Validation list of "Item" in Cell "B3" and "Qty" in cell "D3". I also have a ActiveX Listbox1.

I have the following code which I hoped would work but it only that adds the selected "Item" to the ListBox1 and not both Item and Qty. The code to remove the selected item below it works too. What I am having trouble with is adding the "Qty" as a second column in the ListBox1. I have the ListBox ColumnCount set to 2.

Sub AddItems()
Application.ScreenUpdating = False

Sheets("Uplift").ListBox1.ColumnCount = 2
Sheets("Uplift").ListBox1.ColumnWidths = "200;60"

Item = Sheets("Uplift").Range("B3")
qty = Sheets("Uplift").Range("D3")

If Len(Item) > 0 Then Sheets("Uplift").ListBox1.AddItem (Item) & (qty)

Application.ScreenUpdating = True
End Sub

Sub RemoveItems()
 If Sheets("Uplift").ListBox1.ListIndex >= 0 Then
Sheets("Uplift").ListBox1.RemoveItem Sheets("Uplift").ListBox1.ListIndex
 End If
End Sub

Any ideas

Thanks

Steve

 

[GWteB]

How about

Sub AddItems()
    Application.ScreenUpdating = False

    With Sheets("Uplift")

        .ListBox1.ColumnCount = 2
        .ListBox1.ColumnWidths = "200;60"

        Item = .Range("B3")
        qty = .Range("D3")

        If Len(Item) > 0 Then
            With .ListBox1
                .AddItem
                .List(0, 0) = Item
                .List(0, 1) = qty
            End With
        End If
    End With
    Application.ScreenUpdating = True
End Sub

 

[stirlingmw1]

GWteB

How about

Sub AddItems()
    Application.ScreenUpdating = False

    With Sheets("Uplift")

        .ListBox1.ColumnCount = 2
        .ListBox1.ColumnWidths = "200;60"

        Item = .Range("B3")
        qty = .Range("D3")

        If Len(Item) > 0 Then
            With .ListBox1
                .AddItem
                .List(0, 0) = Item
                .List(0, 1) = qty
            End With
        End If
    End With
    Application.ScreenUpdating = True
End Sub

Thank you for your swift reply, I should have added, the idea is to add multiple items to the ListBox1. I select an item from the list, add a qty and then run the AddItem code. Sadly the code only adds one item and replaces the previous if more are added.

 

[GWteB]

the idea is to add multiple items to the ListBox1.

Seems obvious to me, my example was only meant to show how you can populate multiple columns of a list box, which was your original request.

I select an item from the list, add a qty and then run the AddItem code.

This part isn't clear to me. Do you mean you wanted to populate the list box staged, so by separate actions, like 1st stage left column, 2nd stage right column? Please do elaborate some more on that by defining the steps of the required process.

 

[stirlingmw1]

Seems obvious to me, my example was only meant to show how you can populate multiple columns of a list box, which was your original request.


This part isn't clear to me. Do you mean you wanted to populate the list box staged, so by separate actions, like 1st stage left column, 2nd stage right column? Please do elaborate some more on that by defining the steps of the required process.

GWteB

I do apologies. What I am trying to do is select an "item" from the Validation list in cell "B3", I then add a "qty" in cell "D3", I then run the code to add these 2 items in a single row to the ListBox as your code does. If I then select another "Item" and then "qty" and then run the code again I would like this selection to be added to the ListBox in the next available row under the previous entry, in essence building up a shopping list of items and their quantities.

If I run my original code this works and just keeps adding more and more items to the list, but only works for a single column i.e. the "Item" but omits the "Qty".

 

[GWteB]

I see, try this:

Sub AddItems()

    With Sheets("Uplift")

        .ListBox1.ColumnCount = 2
        .ListBox1.ColumnWidths = "200;60"

        Item = .Range("B3")
        qty = .Range("D3")

        If Len(Item) > 0 Then
            With .ListBox1
                .AddItem
                .List(.ListCount - 1, 0) = Item
                .List(.ListCount - 1, 1) = qty
            End With
        End If
    End With
End Sub

 

[stirlingmw1]

I see, try this:

Sub AddItems()

    With Sheets("Uplift")

        .ListBox1.ColumnCount = 2
        .ListBox1.ColumnWidths = "200;60"

        Item = .Range("B3")
        qty = .Range("D3")

        If Len(Item) > 0 Then
            With .ListBox1
                .AddItem
                .List(.ListCount - 1, 0) = Item
                .List(.ListCount - 1, 1) = qty
            End With
        End If
    End With
End Sub

GWteB

Fantastic, works perfect, thank you so much for your time and passing on your knowledge.

Steve

 

[GWteB]

You are welcome and thanks for the feedback.