asked on

How to exit a sub from a userform "Cancel_Click"

I have code which loads a user form.
The userform displays a list of clients and asks for "ok" or "cancel"
ok continues the routine
cancel should exit the routine.

I'm aiming for this:

If the userform result is ok then continue
else if the userform result is cancel, then end the routine.
?
How does the syntax go?

My code so far within the routine is:
'Load the userform with Clientlist information
ufClientList.Show
____________________________________________________________________

Then, the code for the Userform is:

Private Sub cmd_Cancel_Click()
Unload Me
End Sub
_____________________________________________________________________
Private Sub cmd_Ok_Click()
Unload Me
End Sub
______________________________________________________________________
Private Sub UserForm_Initialize()
'Check that data exists in the correct sheet
If Not Range("A7") Like "acct*" Then
MsgBox "There are no client data"
Exit Sub
Else
Range(Cells(8, 1), Cells(8, 1).End(xlDown)).Name = "Clients"
Me.Lbox_ClientList.RowSource = Range("Clients").Address
End If
End Sub
______________________________________________________________________

So far i have found that either clicking OK, or Cancel leads to the routine continuing.

What is the bit of code I have missed to make it all go away?

Thanks very much!

Rory Archibald

Add this to the top of the userform module

Public Cancelled as Boolean

Open in new window

then alter the button codes to:

Private Sub cmd_Cancel_Click()
Cancelled = True
Me.Hide
End Sub
_____________________________________________________________________
Private Sub cmd_Ok_Click()
Cancelled = False
Me.Hide
End Sub

Open in new window

then your calling code becomes:

ufClientList.Show
if ufClientList.Cancelled then
   unload ufClientList
   Exit Sub
Else
   unload ufClientList
End If

Open in new window

David Phelops

ASKER

Thanks very much for the quick response...

I have change my code to yours:

When the Cancel button is clicked now a "Compile Error: Syntax Error" message is displayed.
Please see screenshot attached...

Have I missed something.....?

Cheers
Compile-Error-VBA.docx

Rory Archibald

You are missing a closing parenthesis after the second Cells(8, 1) in your Initialize sub.

David Phelops

ASKER

The second parenthesis is there, otherwise the userform would not work at all, even when the OK button was clicked. I'd get the message: "Expected List Separator )"

Range(Cells(8, 1), Cells(8, 1).End(xlDown)).Name = "Clients"

i'm very puzzled...

Rory Archibald

Sorry - didn't look at the picture closely enough. Would be much easier to figure out with a workbook (you can remove all data).

David Phelops

ASKER

Thanks... I have attached the file minus all sensitive data.
Most appreciated.
Cheers
David
SampleExcelFile.xlsm

ASKER CERTIFIED SOLUTION

Rory Archibald

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

David Phelops