Exit Sub when cancel button is clicked in UserForm

nikaleya

New Member
Joined
Feb 15, 2021
Messages
9
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

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,812
Office Version
  1. 2010
Platform
  1. Windows
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)
 
Solution

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,709
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
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.
 

nikaleya

New Member
Joined
Feb 15, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
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.
 

nikaleya

New Member
Joined
Feb 15, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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 🤓
 

nikaleya

New Member
Joined
Feb 15, 2021
Messages
9
Office Version
  1. 365
Platform
  1. Windows
@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.
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,812
Office Version
  1. 2010
Platform
  1. Windows
Glad to have helped, I did wonder why it didn't work!!
 

Watch MrExcel Video

Forum statistics

Threads
1,129,816
Messages
5,638,496
Members
417,029
Latest member
lingx86

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
Top