Exit loop within if statement when condition is met in VBA

Question

I am trying to add items to a listbox in a Userform using a Command Button in Excel VBA. The tricky part is that said items should be grouped if the user clicks the button more than once (adding the same item more than once). For example, the first button is named cmdItem1 and has Item 1 as its caption. The listbox is named lstSaleDetail and has two columns, one of them is supposed to register the item's name and the second one its quantity, which starts at 1.

So, the first time any of the buttons is clicked, a new row is added to the listbox with a quantity of 1. But, the second (o any other) time the same button is clicked, instead of adding a new row with a quantity of 1, I want the existing row to update and show a quantity of 2. For this, I tried the following code:

Private Sub cmdItem1_Click()

  If lstSaleDetail.ListCount > 0 Then
  For i = 0 To (lstSaleDetail.ListCount - 1)
  
    If cmdItem1.Caption = lstSaleDetail.List(i) Then
      lstSaleDetail.List(i, 1) = lstSaleDetail.List(i, 1) + 1
      
    Else
      lstSaleDetail.AddItem
      lstSaleDetail.List(lstSaleDetail.ListCount - 1, 0) = cmdItem1.Caption
      lstSaleDetail.List(lstSaleDetail.ListCount - 1, 1) = "1"
      
    End If
  
  Next i
  
  Else
    lstSaleDetail.AddItem
    lstSaleDetail.List(lstSaleDetail.ListCount - 1, 0) = cmdItem1.Caption
    lstSaleDetail.List(lstSaleDetail.ListCount - 1, 1) = "1"
  
  End If

End Sub

This only works if the listbox is empty, in which case the code works perfectly. However, if any other items are added (hence there is more than one row) the code breaks. Although the first row containing the information of the clicked item keeps increasing its quantity the way it should, new rows of the same item are also added and that should not be happening. Any suggestions of what could be wrong with my code?

As a clarification, I added the If lstSaleDetail.ListCount > 0 Then condition because otherwise none of the code works when the listbox is empty.

Tim Williams · Accepted Answer · 2020-11-02 01:10:09Z

1

You can exit the sub as soon as you make a match in the loop. If you get past the loop there was no match, so add as new item:

Private Sub CommandButton1_Click()
    AddToList CommandButton1.Caption
End Sub

Private Sub CommandButton2_Click()
    AddToList CommandButton2.Caption
End Sub

Private Sub CommandButton3_Click()
    AddToList CommandButton3.Caption
End Sub

Private Sub AddToList(capt)

    Dim i As Long
    With lstSaleDetail
        'loop and look for the passed caption
        For i = 0 To (.ListCount - 1)
            If capt = .List(i) Then
                .List(i, 1) = .List(i, 1) + 1
                Exit Sub 'done...
            End If
        Next i
        'if we got here there was no match, so add as new item
        .AddItem capt
        .List(.ListCount - 1, 1) = "1"
    End With
  
End Sub

answered Nov 2, 2020 at 1:10

Tim Williams

169k8 gold badges104 silver badges143 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Fernanda Over a year ago

This works perfectly. Thank you for the help. Theoretically the capt part is just a variable name that can be modified to anything, right?

Tim Williams Over a year ago

Yes you can name that whatever you want.

Fernanda Over a year ago

Thanks! You flawlessly solved my issue. Have a good day Sir.

Collectives™ on Stack Overflow

Exit loop within if statement when condition is met in VBA

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related