Hi,
I am trying to create a macro, where everytime the file is saved, it saves a backup file and a log of the current file. To achieve this, in 'ThisWorkBook' I have
Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
MsgBox "Macro starts"
LockReady
MsgBox "Macro ends"
End Sub
And in 'LockReady' I have
Sub LockReady()
Dim origName As String
Dim origPath As String
Dim backupDest As String
Dim timestamp As String
origName = ThisWorkbook.Name
origPath = ThisWorkbook.Path & "" & ThisWorkbook.Name
backupDest = ThisWorkbook.Path & ""
timestamp = Format(Date, "ddmmyyyy") & "(" & Format(Now, "hhmm") & ")" 'In format DDMMYYYY(HHMM)
Application.DisplayAlerts = False 'Overwrites earlier files without asking
Application.EnableEvents = False 'Does not execute current macro while saving backups
ActiveWorkbook.SaveAs Filename:=backupDest & Split(origName, ".")(0) & "_" & timestamp & ".xlsm" 'The log file
ActiveWorkbook.SaveAs Filename:=backupDest & Split(origName, ".")(0) & "_backup.xlsm" 'The backup file
ActiveWorkbook.SaveAs Filename:=origPath 'Saves with the original name, so it is open after execution.
Application.EnableEvents = True
Application.DisplayAlerts = True
End Sub
From the debug messages I can see, that Excel reaches the "Macro ends", and because I have screen updating on, I can see that it is correctly saving and changing the name of the open workbook. However, after I click 'OK' in the "Macro ends" message box, the Excel shuts down or crashes without any notifications. I even have debug message in 'BeforeClose', but it doesn't appear:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
MsgBox "Closes"
End Sub
Same happens even if I leave out the 'ActiveWorkbook.SaveAs Filename:=origPath'. However, if I execute the same macro with a button and keep 'LockReady' in 'BeforeSave', it works well. Any idea what is happening and how to fix it?
Bookmarks