Results 1 to 4 of 4

Excel closes after executing macro after every save and saving backups inside macro

Threaded View

061elliven Excel closes after executing... 11-18-2022, 04:54 AM
romperstomper Re: Excel closes after... 11-18-2022, 05:02 AM
061elliven Re: Excel closes after... 11-18-2022, 05:12 AM
romperstomper Re: Excel closes after... 11-18-2022, 05:22 AM
  1. #1
    Registered User
    Join Date
    11-18-2022
    Location
    Helsinki, Finland
    MS-Off Ver
    2209
    Posts
    4

    Excel closes after executing macro after every save and saving backups inside macro

    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?
    Last edited by 061elliven; 11-18-2022 at 05:12 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. This macro closes firefox browser. I need a modification that closes Chrome
    By emymeeky in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 03-27-2017, 12:48 PM
  2. [SOLVED] MACRO: Save As Macro Not Saving
    By egarcia7 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 08-09-2016, 04:40 PM
  3. [SOLVED] executing macro causes excel to open the workbook that the macro is associated with
    By YOO629 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 04-02-2015, 11:18 PM
  4. [SOLVED] Need Help changing how the excel window closes to use a macro instead (the red X)
    By LoneWolf3574 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 12-11-2012, 02:56 AM
  5. Auto run macro when excel closes
    By ResulG in forum Excel General
    Replies: 3
    Last Post: 11-27-2006, 01:15 PM
  6. [SOLVED] Excel closes down during a macro
    By Muttley in forum Excel General
    Replies: 0
    Last Post: 01-24-2006, 08:30 AM
  7. [SOLVED] Macro Editor closes Excel 2000 ?
    By Rmontgo in forum Excel General
    Replies: 0
    Last Post: 07-16-2005, 10:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1