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
– MiguelH, Commented Mar 18, 2023 at 9:38

Gustav · Accepted Answer · 2023-03-18 10:26:07Z

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, 2023 at 10:26

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

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

4 Comments

VBasic2008 Over a year ago

Is there a particular reason why you're not using If answer = vbNo ...? BTW, the Else needs to be End If.

Gustav Over a year ago

@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.

Taher Alaa Over a year ago

@Gustav I will try it, but in this case what will the No button do?

Gustav Over a year ago

Yes, it will do.

Collectives™ on Stack Overflow

VBA - Msgbox inside a Loop

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related