Workbook_BeforeClose not working

[Biz]

Dear All,

I am trying to close file and based on user selection, either save file or does not save the file. When vba is run it automatically runs twice which is error if user has selected either Yes, No, Cancel.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim iReply As Byte, iType As Integer
'<ANY code>
Application.DisplayAlerts = False
Application.ScreenUpdating = False
    ' Define buttons argument.
      iType = vbYesNoCancel + vbCritical + vbDefaultButton2
        iReply = MsgBox("Would you like to save now?", _
                        iType)
Select Case iReply
Case Is = vbYes         ' user chose Yes save current workbook
    ThisWorkbook.Close savechanges:=True
Case Is = vbNo           ' user chose No, don't save
    ThisWorkbook.Close savechanges:=False
Case Is = vbCancel      ' user chose Cancel, it stops the code from running
    Exit Sub
End Select
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

What code do I need to use so that code runs only once?

Biz

 

[jasonb75]

An untested guess

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim iReply As Byte, iType As Integer
'
Application.EnableEvents = False
Application.DisplayAlerts = False
Application.ScreenUpdating = False
    ' Define buttons argument.
      iType = vbYesNoCancel + vbCritical + vbDefaultButton2
        iReply = MsgBox("Would you like to save now?", _
                        iType)
Select Case iReply
Case Is = vbYes         ' user chose Yes save current workbook
    ThisWorkbook.Close savechanges:=True
Case Is = vbNo           ' user chose No, don't save
    ThisWorkbook.Close savechanges:=False
End Select
Application.EnableEvents = True
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub

Case for cancel removed, if it's not yes of no then it nust be cancel so do nothing.

Your method of "Exit Sub" was leaving screenupdating and alerts set to false, and would have done the same with events, meaning if the user cancels, the code would not fire next time they try to close.

 

[Biz]

Hi Jasonb75,

Workbook_BeforeClose not firing and msgbox is not appearing.

Biz

 

[GTO]

Kill excel and restart it, or type Application.EnableEvents=True in the Immediete window and press Enter. You may have left events turned off.

Rather than killing events in BeforeClose, you might want to set a flag:

Option Explicit
    
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim iReply As Byte, iType As Integer
Static bolInProcess As Boolean
    
    If Not bolInProcess Then
        
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
        bolInProcess = True
            ' Define buttons argument.
              iType = vbYesNoCancel + vbCritical + vbDefaultButton2
                iReply = MsgBox("Would you like to save now?", _
                                iType)
        Select Case iReply
        Case vbYes         ' user chose Yes save current workbook
            ThisWorkbook.Save
            Application.DisplayAlerts = True
            Application.ScreenUpdating = True
            ThisWorkbook.Close savechanges:=False
        Case vbNo, vbCancel          ' user chose No, don't save
            Application.DisplayAlerts = True
            Application.ScreenUpdating = True
            ThisWorkbook.Close savechanges:=False
        End Select
    End If
End Sub

The Static will retain the flag's value and stop the second loop, but the flag dies harmlessly as the book closes, preventing accidently leaving events shut off.

 

[RoryA]

No need to explicitly close, since you're already closing:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Dim iReply As Byte, iType As Integer
   Application.DisplayAlerts = False
   Application.ScreenUpdating = False
   ' Define buttons argument.
   iType = vbYesNoCancel + vbCritical + vbDefaultButton2
   iReply = MsgBox("Would you like to save now?", _
                   iType)
   Select Case iReply
      Case Is = vbYes         ' user chose Yes save current workbook
         ThisWorkbook.Save
      Case Is = vbNo           ' user chose No, don't save
   End Select
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True
End Sub

 

[GTO]

Oh dear, where's my seeing-eye dog?

 

[Biz]

No need to explicitly close, since you're already closing:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Dim iReply As Byte, iType As Integer
   Application.DisplayAlerts = False
   Application.ScreenUpdating = False
   ' Define buttons argument.
   iType = vbYesNoCancel + vbCritical + vbDefaultButton2
   iReply = MsgBox("Would you like to save now?", _
                   iType)
   Select Case iReply
      Case Is = vbYes         ' user chose Yes save current workbook
         ThisWorkbook.Save
      Case Is = vbNo           ' user chose No, don't save
   End Select
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True
End Sub

Hi Rorya,

Your code works wells but if a person click on Cancel then workbook should not close. It means current work should still be open as user changed his/her mind closing workbook.

Is there a way to fix this problem?

Biz

 

[RoryA]

Private Sub Workbook_BeforeClose(Cancel As Boolean)
   Dim iReply As Byte, iType As Integer
   Application.DisplayAlerts = False
   Application.ScreenUpdating = False
   ' Define buttons argument.
   iType = vbYesNoCancel + vbCritical + vbDefaultButton2
   iReply = MsgBox("Would you like to save now?", _
                   iType)
   Select Case iReply
      Case Is = vbYes         ' user chose Yes save current workbook
         ThisWorkbook.Save
      Case Is = vbNo           ' user chose No, don't save
      Case is = vbcancel
           Cancel = true
End Select
   Application.DisplayAlerts = True
   Application.ScreenUpdating = True
End Sub

Should do it.

 

[Biz]

Hi rorya,

It works. Thank you very much mate.

Biz