MessageBox opens multiple times

loststudent

New Member
Joined
Jan 25, 2018
Messages
7
Hello everyone!

I have a MessageBox in my application that asks whether the user wants to add a new product to the order or not. If I press 'No', it closes. When I press 'Yes', it does what it's supposed to do, lets me add another product, and so on. The problem is that once I press no, the amount of MessageBoxes I get is the number of times I pressed 'Yes' before that. How could I solve this?

The code is:
Code:
'Show MessageBox asking user if they want to add more products to the order
Private Sub Addmoreproducts()
'Declare the msg (message) and ans (answer) variables
Dim msg, ans


Do
Unload UFAddStock
Unload UFUpdateStock
msg = "Add more products to the order?"
ans = MsgBox(msg, vbYesNo)


'If answer is Yes, then close this UserForm and open the UserForm for adding more products
If ans = vbYes Then
Unload UFAddStock
UFUpdateStock.Show

End If

'If answer is No, then close all open UserForms and return to the sheet
If ans = vbNo Then Exit Do
Unload UFAddStock
Unload UFUpdateStock


'Loop for MessageBox until answer is No
Loop Until ans = vbNo


End Sub
Code:
Thank you in advance!
 

Some videos you may like

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

footoo

Well-known Member
Joined
Sep 21, 2016
Messages
2,945
Office Version
2016
Platform
Windows
If ans = vbYes Then
Unload UFAddStock
UFUpdateStock.Show

'If answer is No, then close all open UserForms and return to the sheet
ElseIf ans = vbNo Then
Unload UFAddStock
Unload UFUpdateStock
Exit Do
End If

'Loop for MessageBox until answer is No
Loop
 
Last edited:

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,279
This should work. There were many duplicate, unneeded UnLoadings in the OP code.

How is the sub AddMoreProducts triggered? If it is by a button on UFAddStock or UFUpdateStock, there might be recursion issues. If it is called by the user from the Macro dialog, this should work.

Code:
Private Sub AddMoreProducts()
    Dim msg As String, ans As Long
    Dim msg, ans

    msg = "Add more products to the order?"
    Do
        Unload UFAddStock
        Unload UFUpdateStock
    
        ans = MsgBox(msg, vbYesNo)


       'If answer is Yes, then close this UserForm and open the UserForm for adding more products
        If ans = vbYes Then
            UFUpdateStock.Show
        End If
    Loop Until ans = vbNo
End Sub
 

loststudent

New Member
Joined
Jan 25, 2018
Messages
7
Hi, thank you for the code. Unfortunately, it didn't change anything. The sub is called from another sub.
 

mikerickson

MrExcel MVP
Joined
Jan 15, 2007
Messages
23,279
Do frmUpdateStock and frmAddStock invoke each other?
Does the other sub have any looping that might be where the problem lies?

What happens when you step through the code?
 

Watch MrExcel Video

Forum statistics

Threads
1,095,748
Messages
5,446,261
Members
405,392
Latest member
Steveoaktree1977

This Week's Hot Topics

Top