Stopping code from inside a userform??

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
999
My code calls a userform within which I placed a "Cancel" button intended to stop all code. However, I can't figure out how to make that cancel button actually stop the code. I tried a global boolean but despite being set true in the userform form, it stayed set as false in the code that called the userform. I guess the actual question is how do I pass values from a userform back to the calling code.

I'm at a loss...

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

dmt32

Well-known Member
Joined
Jul 3, 2012
Messages
5,840
Office Version
2019
Platform
Windows
Hi,
Where have you placed the global boolean variable - if it is in your forms code page you need to place it in a standard module.

If still having issues, always helpful to forum if you can publish code having problems with.

Hope Helpful

Dave
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,025
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Set a property of the form, then hide it (do not unload it) and then read the property in the calling code.
 

jmacleary

Well-known Member
Joined
Oct 5, 2015
Messages
955
Office Version
2007
Platform
Windows
Hi there. A global should work. I knocked up a quick bit of code. First this in Module 1:
Code:
Global stopit As Boolean
Sub startup()
stopit = vbFalse
UserForm1.Show
If stopit = vbTrue Then
    MsgBox "stop now"  ' you could put exit sub here
Else
    MsgBox "do some more stuff"
End If
End Sub
and this in the userform for the cancel button:
Code:
Private Sub CommandButton1_Click()
stopit = vbTrue
Unload Me
End Sub
when I ran the subroutine and clicked the button, I got the message "stop now" which meant the global had taken the value. Hope this helps.

John
 

Yongle

Well-known Member
Joined
Mar 11, 2015
Messages
6,060
Office Version
365
Platform
Windows
You can cancel code with
Code:
  End
BUT that may cause you some other issues
eg variable values are lost - but those values can be preserved by dump to worksheet immediately before the code is terminated

Rather than using the End function, a more controlled exit is preferable (I think that is what you are in fact attempting)

If none of the other suggestions (offered in earlier posts above) have solved your problem, explain more fully exactly what you are trying to do ...
1. What is the code doing when you want it stopped?
2. Why do you want it stopped abruptly?
3. Should any variable values to be retained when the code stops?
 
Last edited:

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
999
Hi there. A global should work. I knocked up a quick bit of code. First this in Module 1:
Code:
Global stopit As Boolean
Sub startup()
stopit = vbFalse
UserForm1.Show
If stopit = vbTrue Then
    MsgBox "stop now"  ' you could put exit sub here
Else
    MsgBox "do some more stuff"
End If
End Sub
and this in the userform for the cancel button:
Code:
Private Sub CommandButton1_Click()
stopit = vbTrue
Unload Me
End Sub
when I ran the subroutine and clicked the button, I got the message "stop now" which meant the global had taken the value. Hope this helps.

John
That's what I thought I did as well but the boolean still showed as false after unloading the form. I found a less elegant workaround that suits me for now. If I find the time and motivation I'll try this again.

Thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,099,753
Messages
5,470,571
Members
406,707
Latest member
drkjz

This Week's Hot Topics

Top