• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 848
  • Last Modified:

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!
0
David Phelops
Asked:
David Phelops
  • 4
  • 4
1 Solution
 
Rory ArchibaldCommented:
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

0
 
David PhelopsAuthor Commented:
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
0
 
Rory ArchibaldCommented:
You are missing a closing parenthesis after the second Cells(8, 1) in your Initialize sub.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
David PhelopsAuthor Commented:
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...
0
 
Rory ArchibaldCommented:
Sorry - didn't look at the picture closely enough. Would be much easier to figure out with a workbook (you can remove all data).
0
 
David PhelopsAuthor Commented:
Thanks... I have attached the file minus all sensitive data.
Most appreciated.
Cheers
David
SampleExcelFile.xlsm
0
 
Rory ArchibaldCommented:
Delete the blank looking line between the cmd_Cancel_CLick and cmd_OK_Click routines. Go figure.
0
 
David PhelopsAuthor Commented:
Good Grief... as if it isn't hard enough without blank lines conspiring against us....

Thanks so much for your help.
Cheers
David
0

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now