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

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

"TimT" wrote:

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

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.

"Joel" wrote:

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

"TimT" wrote:

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

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" wrote in message
...
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

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


TimT wrote:
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

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!


"Doug Glancy" wrote:

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" wrote in message
...
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

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

"equiangular" wrote:

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


TimT wrote:
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

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)



"TimT" wrote in message
...
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.

"Joel" wrote:

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

"TimT" wrote:

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

I bet you're thinking of an inputbox.

Joel wrote:

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

"TimT" wrote:

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

--

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




TimT wrote:

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!

"Doug Glancy" wrote:

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" wrote in message
...
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




--

Dave Peterson

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.

--
Regards,
Tom Ogilvy


"Joel" wrote in message
...
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

"TimT" wrote:

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