Userform Terminate

[Guest]

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

 

[Guest]

A mesbox returs a test string not vbYesNo. the ascii string need to be
compared using the strcomp() function

if strcomp(ua,"YES") then

You also want to convert the response to either lower case or upper case.

better
if strcomp(strconv(ua,vbUpperCase),"YES") = 0 then

 

[Guest]

Joel,
Thanks for the quick response.
I might have the order wrong or something here.
When X is clicked, the form unloads immediately and then the messagebox
prompts the user. By that time, regardless of what the user answers to the
prompt the form has already closed and the data is lost.
Am I not catching the event correctly?
I wanted to the form to stay open until the user answers Yes or No so the
data can be saved and the user can have a chance to click the save button.

 

[Doug Glancy]

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

 

[equiangular]

Hi,

You should use QueryClose event instead of Terminate event to cancel
closing the userform
Set Cancel to True to cancel closing

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
If (Me.BusName.Value) <> "" Then
Dim ua As VbMsgBoxResult
ua = MsgBox("Do you wish to save the current data entered
on this form?", vbYesNo)
If ua = vbYes Then Cancel = True
End If
End If
End Sub

 

[Guest]

Thanks Doug!
To answer your question, Yes the form does have a Close button and I wrote
the same code to run if clicked - That works fine.
My concern was if the user clicked the X.
That being said, should I take that into consideration when tweaking the
sample you wrote below?
Let me ask you this... is there a way I can just disable or not show the X
button altogether? That would solve a bunch of my problems!

 

[Guest]

Doug and quiangular,
I got it!
Thank you both!!!!

 

[Bob Phillips]

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Dim ua As Long
If (Me.BusName.Value) = "" Then
Unload Me
Else
ua = MsgBox("Do you wish to save the current data entered on this
form?", vbYesNo)
If ua = vbNo Then Cancel = True
End If
End Sub


--
---
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Dave Peterson]

I bet you're thinking of an inputbox.

A mesbox returs a test string not vbYesNo. the ascii string need to be
compared using the strcomp() function

if strcomp(ua,"YES") then

You also want to convert the response to either lower case or upper case.

better
if strcomp(strconv(ua,vbUpperCase),"YES") = 0 then

 

[Dave Peterson]

You could just beep at them:

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
If CloseMode = vbFormControlMenu Then
Beep
'msgbox "Click the Cancel Key"
Cancel = True
End If
End Sub

Or how about just calling the code associated with the cancel commandbutton--as
a user, that's what I would expect.

Private Sub UserForm_QueryClose(Cancel As Integer, CloseMode As Integer)
Call CommandButton1_Click
End Sub

 

[Tom Ogilvy]

this is a totally erroneous answer Joel:

Demo'd from the immediate window:

? msgbox("",vbYesNo) = vbYes
True

A message box does not return yes or Yes or YES. it returns a long value.

so StrComp has no role to play here.