Exit Sub when cancel button is clicked in UserForm

nikaleya

New Member
Joined
Feb 15, 2021
Messages
13
Office Version
  1. 365
Platform
  1. Windows
Hi everyone,
I know this topic has already been discussed several times, but either none of the threads really answers my question or I just really dont get it - I'm sorry, my bad.

What I am trying to do: I have a workbook with several sheets called "project roadmap". The idea is to have one sheet as input/output form containing all project details, another one containing all data from all projects as a database. My sub "getfromdatabase" copies all necessary information from the database to the output form by storing the values of the database in the corresponding ranges of the form. That's working fine. The userform is loaded at the beginning of the sub to allow the user to choose which project should be loaded (by name or number).

So far, my cancel button action is just unload, but I want cancel to exit the whole "getfromdatabase" sub, so no values are going to be copied.

The main sub looks like this:
VBA Code:
Sub getfromdatabase()
Dim wb As Workbook: Set wb = ThisWorkbook
Dim form As Worksheet: Set form = wb.Worksheets("Form")
Dim database As Worksheet: Set database = wb.Worksheets("Database")
Dim milestones As Worksheet: Set milestones = wb.Worksheets("Milestones Overview")

'########## WHICH ONE SHOULD BE LOADED? ##########################

'Call Userform

frmLoadProject.Show

The cancel button action:

VBA Code:
Private Sub CancelButton_Click()
'close Userform
Unload Me

End Sub

I already find the solution using a cancel as boolean and add an IF statement in the main sub. But honestly, I couldn't figure how and where to define the variable and/or private subs. Could you please help?

Many thanks!
Nika
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
You could do it like this:
VBA Code:
' in the module with your main sub
' at the top of the main module
Public canclflg As Boolean
'add this sub
Sub cancelflag(cancl As Boolean)
canclflg = cancl
End Sub
' in the button code
Private Sub CancelButton_Click()
Call cancelflag(True)
'close Userform
Unload Me
' then when you open the userform
Call cancelflag ( FALSE)
 
Upvote 0
Solution
See the "Hide it, don't unload it" section near the top of this blog post: UserForm1.Show

I'd also recommend reading the whole thing when you have a chance.
 
Upvote 0
Thanks for your reply offthelip.
I tried your code:

VBA Code:
'########### CANCEL FLAG FOR CANCEL BUTTON IN USERFORM #############
Public cancelflg As Boolean

Sub cancelflag(cancl As Boolean)
canclflg = cancl
End Sub

'############ MAIN SUB ############################################

Sub getfromdatabase()

'Set Cancelflg to FALSE
Call cancelflag(False)

'Call Userform
frmLoadProject.Show

Button Code in UserForm:

VBA Code:
Private Sub CancelButton_Click()

'exit sub for getfromdatabase
Call cancelflag(True)

'close Userform
Hide

End Sub

Maybe I got it wrong, but unfortunately, cancelling the userform leaves the cancelflag with FALSE (initial value) , so the sub will not exit.
 
Upvote 0
See the "Hide it, don't unload it" section near the top of this blog post: UserForm1.Show

I'd also recommend reading the whole thing when you have a chance.
Thanks RoryA! I'm a total newbie if it comes to userforms, so it is definitely on my reading list ?
 
Upvote 0
@offthelip
I'm sorry, I was lost in my own code, kinda. I put the FALSE-statement in the wrong place, now it works perfectly.
 
Upvote 0
Glad to have helped, I did wonder why it didn't work!!
 
Upvote 0

Forum statistics

Threads
1,213,552
Messages
6,114,278
Members
448,559
Latest member
MrPJ_Harper

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