Excel-VBA update userform listbox based on existing list

Question

I have a useform with multicolumn listbox which lists items and its quantity. The intent is to be able to dynamically update the quantity by adding or deducting from a textbox input. Below is my current code roughly to realize this. So far it is not working with invalid qualifier error for selected(i). would appreciate any guidance on this

    Private Sub CB_AddOrder_Click()

   Dim j, k, qty As Integer
   Dim i As Variant

   qty = TB_Qty.Value

   If qty = 0 Then
   Exit Sub
   End If

   j = LB_Order.ListCount - 1

   Debug.Print j

   If j < 0 Then
   j = 0
   End If

   'Iterate to check if selected menu already existed in ordered list

   For i = 0 To LB_Menu.ListCount - 1
   If LB_Menu.Selected(i) = True Then
   Debug.Print Selected(i)

   For k = 0 To j
   If LB_Menu.Selected(i).List(i, 0) = LB_Order.List(k, 0) Then
   LB_Order.List(k, 3) = LB_Order.List(k, 3).Value + qty
   Exit Sub
   End If
   Next k
        
   With LB_Order
   .ColumnCount = 5
   .ColumnWidths = "120;60;60;60;60"
   .AddItem
   .List(j, 0) = LB_Menu.List(i, 0)
   .List(j, 1) = LB_Menu.List(i, 1)
   .List(j, 2) = LB_Menu.List(i, 2)
   .List(j, 3) = qty
   .List(j, 4) = Format(qty * LB_Menu.List(i, 2), "0.00")
    End With
    End If

    Next i

    End sub

The qualifier error is because you can't add .List(i, 0) as a qualifier to Selected(i). You'll have to grab the selected index and store the .List(i, 0) in a variable, then use that to compare to LB_Order.List(k, 0) if your If statement. — PeterT
– PeterT, Commented Feb 20, 2021 at 16:04
@PeterT thank you for the feedback n apologies for the late reply. i've introduced a new variable to perform the comparison but now having an object required error. May you show me how you would have written it — Muhammad Ikhwan Azmi
– Muhammad Ikhwan Azmi, Commented Feb 28, 2021 at 7:26

PeterT · Accepted Answer · 2021-02-28 19:59:23Z

The confusion you're having relates from the difference in which listbox item(s) are selected and the value of those selected item(s). So when you check for Selected:

Dim i As Long
For i = 0 To LB_Menu.ListCount - 1
    If LB_Menu.Selected(i) Then
        Debug.Print "Menu selected (" & i & ") = " & LB_Menu.List(i, 0)
    End If
Next i

Once you determine which index (i in this case) is selected, you refer to the value by using the index into the List.

The Object Required error you received is because your statement

LB_Order.List(k, 3) = LB_Order.List(k, 3).Value + qty

is using .Value for the list item. This item is a value, not an object.

Here is your sub rewritten as an example. Notice that I'm using single-character variables as loop indexes (which is good), but not as a meaningful value. I renamed other variables in (a hopefully) meaningful way to make your code a little more self-documenting.

Option Explicit

Private Sub CB_AddOrder_Click()
    Dim additionalQty As Long
    additionalQty = TB_Qty.Value

    If additionalQty = 0 Then
        Exit Sub
    End If

    Dim countOfOrderItems As Long
    countOfOrderItems = LB_Order.ListCount - 1
    If countOfOrderItems < 0 Then
        countOfOrderItems = 0
    End If

    'Iterate to check if selected menu already existed in ordered list
    Dim i As Long
    For i = 0 To LB_Menu.ListCount - 1
        If LB_Menu.Selected(i) Then
            Debug.Print "Menu selected (" & i & ") = " & LB_Menu.List(i, 0)
            
            '--- find the matching item and increase the quantity
            Dim k As Long
            For k = 0 To countOfOrderItems
                If LB_Menu.List(i) = LB_Order.List(k, 0) Then
                    LB_Order.List(k, 3) = LB_Order.List(k, 3) + additionalQty
                    Exit Sub
                End If
            Next k
        
            '--- append the new item from the Menu to the Order
            With LB_Order
                .ColumnCount = 5
                .ColumnWidths = "120;60;60;60;60"
                .AddItem
                .List(countOfOrderItems, 0) = LB_Menu.List(i, 0)
                .List(countOfOrderItems, 1) = LB_Menu.List(i, 1)
                .List(countOfOrderItems, 2) = LB_Menu.List(i, 2)
                .List(countOfOrderItems, 3) = additionalQty
                .List(countOfOrderItems, 4) = Format(additionalQty * LB_Menu.List(i, 2), "0.00")
            End With
        End If
    Next i
End Sub

By the way, make sure all of the columns in your listboxes are initialized with values if you will add/subtract number values. If they are just Null, you'll get a Could not set the List property error.

Collectives™ on Stack Overflow

Excel-VBA update userform listbox based on existing list

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related