Which event is it?

Bit of a Monday morning one this. I have a user form which has an OK and a Cancel button, no problems trapping these events and
executing the correct code, but users being users are clicking the x button on the titlebar which is sending the program down the
wrong execution path (it assumes that the OK button has been clicked and carries on executing).

Does anyone know what the event is called when you click the x button, or what form property to check for?

TIA

John Bilton

 

Brain in gear now UserForm_Queryclose!!!

John Bilton

 

UserForm_Terminate

 

That seemed to trap all types of exit from the form. If the OK button is clicked it needs to execute the next section. If cancel
is clicked it needs to run a cleanup routine and go back to the previous module. I'm already using Terminate to exit Excel silently
ExcelApp.Visible = False
ExcelApp.Quit
It's just the point where a user clicks the red X button in the top corner was confusing my poor little bit of VB code <g>

John Bilton

 

Another method is you can wrap the form either thru a simple object or
write a class mod so the OK button passes back a value to evaluate in order
to proceed. For the simple example:

'On a form add:
Private mbData As Boolean
Public Property Get Proceed() As Boolean
Proceed = mbData
End Property
Public Property Let Proceed(mData As Boolean)
mbData = mData
End Property
Private Sub CommandButton1_Click()
'CANCEL button
Proceed = False
Me.Hide
End Sub
Private Sub CommandButton2_Click()
'OK button
Proceed = True
Me.Hide
End Sub

'In a module add:
Public Sub Main()
Dim oFrm As New UserForm1
oFrm.Show
If oFrm.Proceed = True Then
'User hit okay
Else
'Something terminated it
End If
'Clean up before leaving
Unload oFrm
Set oFrm = Nothing
End Sub

 

One for my next project....

Cheers

John Bilton