Userform Terminate
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 |
Userform Terminate
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 |
Userform Terminate
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 |
Userform Terminate
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 |
Userform Terminate
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 |
Userform Terminate
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 |
Userform Terminate
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 |
Userform Terminate
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 |
Userform Terminate
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 |
Userform Terminate
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 |
Userform Terminate
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 |
All times are GMT +1. The time now is 04:13 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com