close userform and cancel sub it was called from

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723
With the cash register in mind, I have a userform to enter different tickets and donations sold for our Relay for Life. After the total due is shown the appropriate tender key is clicked. This in turn brings up another userform to collect info for the tender selected (Cash, Checks, Credit). Problem is if the user pressed the wrong tender (ie. Cash instead of Check) I can't figure out how to tell the calling sub to abort and not record the transaction. Of course the event is tomorrow... TIA

sub in userform Entries:
VBA Code:
Private Sub CB5_Click() 'CASH
Dim WS As Worksheet

'HANDLE CASH TENDERED
CshTndr.TB1 = Me.TB0
CshTndr.Show
'=============================

'HOW TO ABORT SUB IF CANCELED FROM
'TENDERED USERFORM

'=============================
'GO TO SHEET ENTRIES AND RECORD TRANSACTION
Set WS = Sheets("entries")
WS.Select  'TAKE OUT AFTER PROOFING
' COLLECT COUNT OF TYPE OF SALES & TRANSACTIONS
With Sheets("entries")
...code continues

Above code calls userform below:

If user wants to cancel the "Tendered" form then this would be the Userform "CshTndr" section to cancel.

VBA Code:
Private Sub CB1_Click()
Rsp = MsgBox("Are you sure you want to cancel this transaction?", vbYesNo)
If Rsp = vbYes Then
     Unload me
     'AND ADD SOMETHING TO CANCEL CALLING SUB FROM CONTINUING
End If
End Sub

So what could I do in this second snippit to pass back to the first sub?
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.

Trebor76

Well-known Member
Joined
Jul 23, 2007
Messages
4,832
Hi there,

Have a hidden text box (I've called it txtAbort) on the user form (I've used UserForm1) that opens the CshTndr form which is populated by the user's response like so:

VBA Code:
Option Explicit
Private Sub CB5_Click_Click()

    'HANDLE CASH TENDERED
    CshTndr.TB1 = Me.TB0
    CshTndr.Show
    '=============================
    'ABORT SUB IF CANCELED FROM
    'TENDERED USERFORM
    If Me.txtAbort = "Yes" Then 'txtAbort' is hidden
        Exit Sub
    End If
    '=============================

End Sub

VBA Code:
Option Explicit
Private Sub CB1_Click()

    If MsgBox("Are you sure you want to cancel this transaction?", vbYesNo) = vbYes Then
        UserForm1.txtAbort = "Yes"
    Else
        UserForm1.txtAbort = "No"
    End If
    
    Unload Me

End Sub

I also change the back colour of any text boxes I set their visible property to false as a flag when I next open the form.

You could also have a public boolean variable but I'm not a huge fan of those I must say.

HTH

Robert
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,462
Office Version
  1. 2013
Platform
  1. Windows
Both Userforms can interact with each other when you implement properties.
Example for the first Userform (the caller):
VBA Code:
Option Explicit

Private mbCallCancel  As Boolean


Public Property Let CallCancel(ByVal argCancel As Boolean)
    mbCallCancel = argCancel
End Property


Private Sub CommandButton1_Click()

    Dim usfTwo      As UserForm2
   
    mbCallCancel = False
    Set usfTwo = New UserForm2
    With usfTwo
        Set .Caller = Me
        .Show
        If mbCallCancel Then goto SUB_EXIT
    End With

SUB_EXIT:
    Set usfTwo = Nothing
End Sub
Example for the Userform that's been called:
VBA Code:
Option Explicit

Private moCaller        As Object

Property Set Caller(ByRef argUsf As Object)
    Set moCaller = argUsf
End Property


Private Sub CommandButton1_Click()
    Rsp = MsgBox("Are you sure you want to cancel this transaction?", vbYesNo)
    If Rsp = vbYes Then
        moCaller.CallCancel = True      ' <<<<<<<<<<
        Unload Me
    End If
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        moCaller.CallCancel = True      ' <<<<<<<<<<<
    End If
End Sub

Private Sub UserForm_Terminate()
    Set moCaller = Nothing
End Sub
 
Last edited:

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723
Hi there,

Have a hidden text box (I've called it txtAbort) on the user form (I've used UserForm1) that opens the CshTndr form which is populated by the user's response like so:

VBA Code:
Option Explicit
Private Sub CB5_Click_Click()

    'HANDLE CASH TENDERED
    CshTndr.TB1 = Me.TB0
    CshTndr.Show
    '=============================
    'ABORT SUB IF CANCELED FROM
    'TENDERED USERFORM
    If Me.txtAbort = "Yes" Then 'txtAbort' is hidden
        Exit Sub
    End If
    '=============================

End Sub

VBA Code:
Option Explicit
Private Sub CB1_Click()

    If MsgBox("Are you sure you want to cancel this transaction?", vbYesNo) = vbYes Then
        UserForm1.txtAbort = "Yes"
    Else
        UserForm1.txtAbort = "No"
    End If
   
    Unload Me

End Sub

I also change the back colour of any text boxes I set their visible property to false as a flag when I next open the form.

You could also have a public boolean variable but I'm not a huge fan of those I must say.

HTH

Robert

Simple enough for me! Thank you for the prompt solution.
 

TryingToLearn

Well-known Member
Joined
Sep 10, 2003
Messages
723
Both Userforms can interact with each other when you implement properties.
Example for the first Userform (the caller):
VBA Code:
Option Explicit

Private mbCallCancel  As Boolean


Public Property Let CallCancel(ByVal argCancel As Boolean)
    mbCallCancel = argCancel
End Property


Private Sub CommandButton1_Click()

    Dim usfTwo      As UserForm2
  
    mbCallCancel = False
    Set usfTwo = New UserForm2
    With usfTwo
        Set .Caller = Me
        .Show
        If mbCallCancel Then goto SUB_EXIT
    End With

SUB_EXIT:
    Set usfTwo = Nothing
End Sub
Example for the Userform that's been called:
VBA Code:
Option Explicit

Private moCaller        As Object

Property Set Caller(ByRef argUsf As Object)
    Set moCaller = argUsf
End Property


Private Sub CommandButton1_Click()
    Rsp = MsgBox("Are you sure you want to cancel this transaction?", vbYesNo)
    If Rsp = vbYes Then
        moCaller.CallCancel = True      ' <<<<<<<<<<
        Unload Me
    End If
End Sub

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    If CloseMode = vbFormControlMenu Then
        moCaller.CallCancel = True      ' <<<<<<<<<<<
    End If
End Sub

Private Sub UserForm_Terminate()
    Set moCaller = Nothing
End Sub

Thank you GWteB. I like the idea of learning about using the properties and will look at this another time since Trebor's method is one I understand already.
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,462
Office Version
  1. 2013
Platform
  1. Windows
You are welcome and thanks for letting us know.
 

Forum statistics

Threads
1,148,282
Messages
5,745,836
Members
423,981
Latest member
ph1l

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