I have a recursion problem in a workbook that comes up only when a second workbook is open at the same time and I click close on the first workbook, at which time Microsoft asks if I want to save the workbook. If I say yes it will come back and ask me again. This will repeat until I click on Cancel. I find that when I finally click cancel the workbook is actually saved. Then when I click on close the workbook will close without incident.
In the following code the subroutine FSO_File_Backup is not requested (it does not update the workbook in any case).
If I save the file workbook first and then click on close the problem does not arise.
I hope my description of the problem is clear.
In the following code the subroutine FSO_File_Backup is not requested (it does not update the workbook in any case).
If I save the file workbook first and then click on close the problem does not arise.
I hope my description of the problem is clear.
VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
On_Close = False
If ThisWorkbook.Saved = True Then
Resp = MsgBox("Do you wish to backup this workbook?", vbYesNo, "Backup?")
If Resp = vbYes Then
FSO_File_Backup
End If
Else
On_Close = True
End If
End Sub
________________________________________________________________________________________________________________________
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
' This will cancel the user-requested save event and then hide the business sheets,
' show the setup (instruction) sheet, save workbook via this macro,
' and then set the business screens back again.
uSheet = ActiveSheet.Name
Cancel = True
Application.ScreenUpdating = False
' Set up sheets for close configuration
On Error GoTo Screwed_up
Sheets("Startup").Visible = xlSheetVisible
For Each ws In Worksheets
If Not ws.Name = "Startup" Then
ws.Visible = xlSheetVeryHidden
End If
Next
On Error GoTo 0
Application.EnableEvents = False ' Prevent recursion
ActiveWorkbook.Save
Application.EnableEvents = True
' Put everything back the way it was
If Not On_Close Then
If Not uSheet = "Reconcile" Then
For Each ws In Worksheets
If Not ws.Name = "Startup" And _
Not ws.Name = "Reconcile" Then
ws.Visible = xlSheetVisible
End If
Next
Else
Sheets(uSheet).Visible = xlSheetVisible
End If
Sheets("Startup").Visible = xlSheetVeryHidden
Sheets(uSheet).Select
End If
Application.ScreenUpdating = True
ActiveWorkbook.Saved = True ' Make excel think workbook is saved
If On_Close Then
Resp = MsgBox("Do you wish to backup this workbook?", vbYesNo, "Backup?")
If Resp = vbYes Then
FSO_File_Backup
End If
End If
GoTo Skip_it
Screwed_up:
MsgBox "Error: " & Err.Number & "/" & Err.Description
Skip_it:
End Sub