Exit Sub when cancel button is clicked in UserForm

[nikaleya]

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:

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:

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

 

[offthelip]

You could do it like this:

' 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)

 

[RoryA]

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]

Thanks for your reply offthelip.
I tried your 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:

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]

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]

@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]

Glad to have helped, I did wonder why it didn't work!!