Re: Userform Terminate

Tech-Archive recommends: Speed Up your PC by fixing your registry

Tim,

In the Terminate event, even if you exit the sub, the form still terminates.
What you want is the QueryClose event. It has a Cancel argument, which you
set to True if you want the Close to be cancelled, i.e., the form to stay
active.

The code below is a basic structure that you can modify, I think, to suit
your purposes. CloseMode refers to how the form is closed. I was not sure
if your users can close in other ways than the "x" on the form, and if you
want them to be prompted in those other cases. This assumes that you only
want them prompted if they clicked the "x". You could delete the If
CloseMode... lines if you want them prompted no matter how the form is
closed:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
If MsgBox("Do you want to Close?", vbYesNo) = vbNo Then
Cancel = True
End If
End If
End Sub

hth,

Doug

"TimT" <TimT@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0E29006B-6688-41BB-BE53-4F2D8C973175@xxxxxxxxxxxxxxxx
Hey Troops,
I would like to give the user the option to abort a Terminate (clicking
the
red X) if they choose.
If there is data that the user input and they accidently terminate I was
trying to give the user the choice to abort the close and run another sub
but
the form still closes and then runs the msgbox part of the code.

here's my code, any help would be greatly appreciated!!:

Private Sub UserForm_Terminate()

If (Me.BusName.Value) = "" Then
Unload Me
End If

If (Me.BusName.Value) <> "" Then

Dim ua As String
ua = MsgBox("Do you wish to save the current data entered on this
form?", vbYesNo)
If ua = vbYes Then
Exit Sub
Else
If ua = vbNo Then
Unload Me
End If
End If
End If
End Sub


.

Relevant Pages

  • Re: Userform Terminate
    ... In the Terminate event, even if you exit the sub, the form still terminates. ... It has a Cancel argument, ... CloseMode refers to how the form is closed. ...
    (microsoft.public.excel.programming)
  • Re: Userform Terminate
    ... Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) ... 'msgbox "Click the Cancel Key" ... In the Terminate event, even if you exit the sub, the form still terminates. ...
    (microsoft.public.excel.programming)
  • Re: Disable the Close button on a Userform
    ... If CloseMode = vbFormControlMenu Then ... Cancel = True ... End Sub ...
    (microsoft.public.excel.programming)
  • Re: Capture a cancel button
    ... > Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) ... >> When i click the button the unprotect sheet ... the problem is that if i click the 'Cancel' button the rest ...
    (microsoft.public.excel.programming)
  • Re: x-button on userform
    ... You have Cancel and Closemode the wrong way round in the Sub statement. ... Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer) ... > The problem is that when the user presse "yes", I don't want the userform ...
    (microsoft.public.word.vba.userforms)