Exiting a routine when clicking cancel on a userform

[Pauljj]

Dear all, I have tried looking at existing posts but so far nothing works

I have a user form that has an OK and Cancel button. When I click on cancel, the form unloads but still moves onto the remaining part of the code. I have tried the following without success

In my main module

Public cancflg as boolean

--------------------------------------------------------------
Sub cancelflag(cancl as boolean)
canclflg = cancel
End sub
--------------------------------------------------------------

In the userform, when clicking Cancel

Private Sub CommandButton1_Click

Call cancelflag(true)
Unload me
End sub

In addition to this

Private Sub Userform_Initialize()

Call Cancelflag(False)

End Sub

In my main code back in the module

FrmInput.show

If Cancelflag = True then

Exit sub

End if

Is there something I have missed ?

 

[dmt32]

Hi,
quick glance & note you have a typo in both parameter & variable names

Public cancflg As Boolean


Sub cancelflag(cancel As Boolean)
cancflg = cancel
End Sub

and need to test Public boolean variable

frmInput.Show

If cancflg = True Then Exit Sub

'rest code

Dave

 

[RoryA]

Your form should not unload itself. It should just hide. You can then test its cancflag member before unloading it, and exiting if necessary.

 

[Pauljj]

Thank you both for your replies. I have made the changes to the typos, thanks dmt32. When I step through the code, this is what happens

1) On Initialise CANCFLG is set to FALSE
2) This calls the Cancelflag routine
3) Form appears
4) CANCFLG now appears as <Out of Context>
5) I click cancel
6) CANCFLG now appears as FALSE
7) This calls the Cancelflag routine
8) CANCFLG now appears as TRUE
9) Upon retuning to the cancel click routine CANCFLG has returned to <Out of Context>

 

[dmt32]

Hi,
if you just want your buttons to respond in similar way to a msgbox then you could just use the msgbox enumeration

example

code in your form

Private Sub CommandButton1_Click()
    Me.Hide
    Me.Tag = vbCancel
End Sub

Private Sub CommandButton2_Click()
    Me.Hide
    Me.Tag = vbOK
End Sub

Function GetResponse() As VbMsgBoxResult
    Me.Show
    GetResponse = Val(Me.Tag)
    Unload Me
End Function

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    'prevent close by X
    Cancel = CloseMode = 0
End Sub

code in your standard module

Sub Pauljj()

    Dim Response As VbMsgBoxResult

    Response = frmInput.GetResponse
    
    'cancel pressed
    If Response = vbCancel Then Exit Sub
    
    'rest of code
    MsgBox "Hello"
    

End Sub

You can also create your own custom Enumeration if preferred

Hope Helpful

Dave

 

[Pauljj]

Hi,
if you just want your buttons to respond in similar way to a msgbox then you could just use the msgbox enumeration

example

code in your form

Private Sub CommandButton1_Click()
    Me.Hide
    Me.Tag = vbCancel
End Sub

Private Sub CommandButton2_Click()
    Me.Hide
    Me.Tag = vbOK
End Sub

Function GetResponse() As VbMsgBoxResult
    Me.Show
    GetResponse = Val(Me.Tag)
    Unload Me
End Function

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
    'prevent close by X
    Cancel = CloseMode = 0
End Sub

code in your standard module

Sub Pauljj()

    Dim Response As VbMsgBoxResult

    Response = frmInput.GetResponse
   
    'cancel pressed
    If Response = vbCancel Then Exit Sub
   
    'rest of code
    MsgBox "Hello"
   

End Sub

You can also create your own custom Enumeration if preferred

Hope Helpful

Dave

Thank you for this Dave, I will give this a try. One question I have, I do have other code being carried out if the user clicks OK, would this still work ? I guess it's best I just try

 

[dmt32]

Thank you for this Dave, I will give this a try. One question I have, I do have other code being carried out if the user clicks OK, would this still work ? I guess it's best I just try

Hi,
suggested idea acts in same way as the msgbox function in that it just returns an integer value based on which button is pressed.
In my example, Cancel button will return 2 (vbCancel) and OK button will return 1 (vbOK)

Your other code for an OK response would be placed where "rest of code" is shown

 Response = frmInput.GetResponse()
    
    'cancel pressed
    If Response = vbCancel Then
    
        Exit Sub
        
    Else

    'OK pressed
    'rest of code
    MsgBox "Hello"
    
    
    End If

Dave

 

[Pauljj]

Worked a treat, thank you very much

 

[dmt32]

Worked a treat, thank you very much

welcome - appreciate feedback

Dave