VBA - Msgbox inside a Loop

Question

I have the attached userform. Whenever I click on the "Proceed!" button, I have it check if the sum of batches weight is more than the first assigned weight in the Product Details frame;

if yes it asks me to proceed or not, I the answer was "Yes" it continues, If "No" I want it to let me change the quantities again in the userform.

I have written the below code but when I hit "No" the msgbox keeps showing again and again:

Private Sub CommandButton1_Click()     'Proceed! Button
Dim answer As Integer

q = Val(Left(Label2.Caption, 5))       'Weight in Product Details --> 15.12 tons 

Total = BatchTotal1 + BatchTotal2 + BatchTotal3 + BatchTotal4 + BatchTotal5  'Publicly dimmed previously

Again:

If Total > q Then
    answer = MsgBox("Batches total weight is more than you assigned first, Do you want to proceed?", vbQuestion + vbYesNo)
    If answer = vbYes Then
        GoTo Continue
    Else
        GoTo Again
    End If
End If

Continue:

'Another code

How do you recalculate q or Total if you never return to this in your loop. Possibly a Do - While loop might be better than Goto? — MiguelH, Mar 18 '23 at 09:38

score 0 · Accepted Answer · answered Mar 18 '23 at 10:26

0

Avoid GoTo if at all possible. Try this:

Private Sub CommandButton1_Click()     'Proceed! Button

    Dim answer As Long

    q = Val(Left(Label2.Caption, 5))       'Weight in Product Details --> 15.12 tons 

    Total = BatchTotal1 + BatchTotal2 + BatchTotal3 + BatchTotal4 + BatchTotal5  'Publicly dimmed previously
    If Total > q Then
        answer = MsgBox("Batches total weight is more than you assigned first, Do you want to proceed?", vbQuestion + vbYesNo)
        If answer <> vbYes Then
            Exit Sub
        Else
    End If

    ' Another code

End Sub

answered Mar 18 '23 at 10:26

Gustav

53,498
7
29
55

Is there a particular reason why you're not using `If answer = vbNo ...`? BTW, the `Else` needs to be `End If`. – VBasic2008 Mar 18 '23 at 10:41
1

@VBasic2008: Well, it required less editing ... but it will also let the code work as intended in case `vbYesNoCancel` should be used for some reason. – Gustav Mar 18 '23 at 10:58
@Gustav I will try it, but in this case what will the No button do? – Taher Alaa Mar 18 '23 at 13:11
Yes, it will do. – Gustav Mar 19 '23 at 10:45

VBA - Msgbox inside a Loop

1 Answers1