MrExcel.com - Your One Stop for Excel Tips & Solutions

Cancel out of a quit process (I will clarify)

Posted by Christopher Mains on July 25, 2001 7:53 AM

Ok I have set up a form to show up when you close excel it then askes you if you want to close excel, return to the spreadsheet your working on or to open a new spreadsheet. Now one the final two selections I need to cancel the "close" process that is started by clicking on the X in the upper right corner. The reason I ask is because if I select return to spreadsheet or open new it will then ask me if I want to save and you then have to click cancel if you want to continue working. All help is appreciated.

Posted by Jim on July 25, 2001 8:01 AM

Try using "Application.DisplayAlerts = False

Posted by Christopher Mains on July 25, 2001 8:03 AM


way ahead of you. doesn't work thanks though

Posted by Jim on July 25, 2001 8:14 AM

Assuming your code looks something like this it should work:
Dim Msg As String, Ans As String
If Not Me.Saved Then
Msg = "Do yo want to save the changes made to"
Msg = Msg & Me.Name & "?"
Ans = MsgBox(Msg, vbQuestion + vbYesNoCancel)
Select Case Ans
Case vbYes
M.Save
Case vbNo
Me.Saved = True
Case vbCancel
Cancel = True
Exit Sub
End Select
End If
End Sub

Posted by Christopher Mains on July 25, 2001 8:23 AM

my code is very simple.

I have made a form called UserForm1

in ThisWorkBook

Public Sub Workbook_BeforeClose(Cancel As Boolean)
UserForm1.Show
End Sub

in the form I have

Private Sub CommandButton1_Click()
bClose = True
ActiveWorkbook.Save
Unload UserForm1
Application.Quit
End Sub
Private Sub CommandButton2_Click()
Unload UserForm1

End Sub
Private Sub CommandButton3_Click()
Call Sheet1.NewWork
Unload UserForm1
End Sub

Posted by Ivan F Moala on July 26, 2001 2:49 AM

Try this........

Private Sub CommandButton1_Click()
bClose = True
ActiveWorkbook.Save
Unload UserForm1
Application.Quit
End Sub

Private Sub CommandButton2_Click()
Unload UserForm1
ShouldWe 0, False
End Sub

Private Sub CommandButton3_Click()
Call Sheet1.NewWork
Unload UserForm1
ShouldWe 0, False
End Sub

Then in a Module add this Function

Static Function ShouldWe(From, Optional CloseIt As Boolean) As Boolean
'From = any number > 0 means it is called from another routine
' otherwise it was called from the userform
If From > 0 Then
If CloseIt = False Then
ShouldWe = False
Else
ShouldWe = True
End If
End If
End Function


Your Thisworkbook code should then be;

Private Sub Workbook_BeforeClose(Cancel As Boolean)
UserForm1.Show
If Not ShouldWe(1) Then Cancel = True
End Sub

Ivan

Posted by Ivan F Moala on July 26, 2001 3:13 AM

Missed one small piece of code

should add
Application.enableevents=false

IN HERE >>>> Try this........ Private Sub CommandButton1_Click()
>>>>>