Stopping code from inside a userform??

roscoe

Well-known Member
Joined
Jun 4, 2002
Messages
1,041
Office Version
  1. 365
Platform
  1. Windows
  2. MacOS
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
 

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.
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
 
Upvote 0
Set a property of the form, then hide it (do not unload it) and then read the property in the calling code.
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,484
Messages
6,113,920
Members
448,533
Latest member
thietbibeboiwasaco

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top