Workbook slow to close - any obvious issues with the code?

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
Hi

This is my workbook_before close event code.

I'd be grateful to know if the 20 seconds or so it takes to close can be expected with the below code or if there's something obvious that can be amended to speed it up?

Many thanks!

VBA Code:
Private Sub workbook_beforeclose(Cancel As Boolean)

Dim MsgResult As Integer

Application.EnableEvents = False

MsgResult = MsgBox("Are you SURE you want to overwrite the master Exercise Log file?   ", vbYesNoCancel + vbExclamation, "WARNING")

Select Case MsgResult

    Case vbNo
        'a) If you select No, you get a second dialog box saying "Existing file unchanged" and the workbook as well as Excel will close.
        
        'checks to see if other workbooks are open.  It will not shut down the application if there are other workbooks open
        If Application.Workbooks.Count < 2 Then
            MsgBox "Master file unchanged - data NOT saved" & vbNewLine _
            & "" & vbNewLine _
            & "Exercise Log will now close", vbInformation, "Master File Unchanged "
            'the following line does not save the workbook but sets a bit that tells Excel that any changes have already been saved, even if the changes were not actually saved
            ThisWorkbook.Saved = True
            Application.Quit
        Else
            'other workbooks are open.  Leave the application alone and
            'simply close this workbook.
            MsgBox "Other workbooks open - data NOT saved!" & vbNewLine _
            & "" & vbNewLine _
            & "Exercise Log will now close!", vbInformation, "Master File Unchanged "
            Me.Close False
        End If
        
    Case vbCancel
        'b) If I select Cancel then there's no action, the dialog box closes and the workbook remains open as if nothing had happened.
        
        Cancel = True
        'simply cancel the closing of this workbook
         
    Case vbYes
        'c) If I select Yes, then the new data is saved, a second dialog box appears + vbconf with existing data overwritten.
        '   The file and Excel then close.
   
'Bernie Dietrick
Dim bdFileName As String
Dim FullFileName  As String

Application.DisplayAlerts = False

FullFileName = ActiveWorkbook.FullName
bdFileName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)

If LCase(Application.UserName) <> "jsullivan" Then
   ActiveWorkbook.SaveCopyAs Filename:="E:\BACKUPS\Exercise Log\" & _
      bdFileName & " Backup - " & Format(Now, "dddd dd mmmm yyyy, h.mmam/pm") & _
      ".xlsm"

   'Backup to 128gb USB drive as well
   ActiveWorkbook.SaveCopyAs Filename:="Y:\DOCUMENTS\EXERCISE LOG\Exit Backups\" & _
      bdFileName & " Backup - " & Format(Now, "dddd dd mmmm yyyy, h.mmam/pm") & _
      ".xlsm"

End If

ActiveWorkbook.SaveAs Filename:=FullFileName, AddToMru:=False

Application.DisplayAlerts = True

        Worksheets("Training Log").[H1:H9] = vbNullString
        'Worksheets("Analysis").[G1:Z1] = vbNullString
        'Worksheets("Iron Man Log").[G1:Z1] = vbNullString
        Worksheets("Daily Tracking").[CI1:CZ1] = vbNullString
        Worksheets("Indoor Bike").[I1:I2] = vbNullString
        
        'Application.EnableEvents = True 'commented out 20.09.2021
        MsgBox "New backup files created in" & vbNewLine & vbNewLine _
        & "E:\BACKUPS\EXERCISE LOG    " & vbNewLine & vbNewLine _
        & "Y:\DOCUMENTS\EXERCISE LOG\EXIT BACKUPS" & vbNewLine & vbNewLine _
        & "Exercise Log will now close", vbInformation, "Master File Overwritten"
        
         ThisWorkbook.Saved = True
         'Application.EnableEvents = True
        
End Select

Application.EnableEvents = True

End Sub
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
Ah, OK.

Well the Y drive is and always has been a USB drive, so absolutely. The E drive has always been an internal hard drive, which would have been FAT32 years ago, but has long since been NTFS.

Sure:
VBA Code:
Private Sub workbook_beforeclose(Cancel As Boolean)
'
    Dim MsgResult As Long
'
'   Turn Settings off
      Application.ScreenUpdating = False                                    ' Turn Screen Updating off
         Application.Calculation = xlCalculationManual                      ' Turn AutoCalculation off
        Application.EnableEvents = False                                    ' Turn EnableEvents off
'
    MsgResult = MsgBox("Are you SURE you want to overwrite the master Exercise Log file?   ", vbYesNoCancel + vbExclamation, "WARNING")
'
    Select Case MsgResult
        Case vbNo
'           a) If you select No, you get a second dialog box saying "Existing file unchanged" and the workbook as well as Excel will close.
'
'           Checks to see if other workbooks are open.  It will not shut down the application if there are other workbooks open
            If Application.Workbooks.Count < 2 Then
                MsgBox "Master file unchanged - data NOT saved" & vbNewLine & "" & vbNewLine & "Exercise Log will now close", vbInformation, "Master File Unchanged "
'
'               The following line does not save the workbook but sets a bit that tells Excel that any changes have already been saved, even if the changes were not actually saved
'
                ThisWorkbook.Saved = True
'
                Application.Quit
            Else
'               Other workbooks are open.  Leave the application alone and simply close this workbook.
                MsgBox "Other workbooks open - data NOT saved!" & vbNewLine & "" & vbNewLine & "Exercise Log will now close!", vbInformation, "Master File Unchanged "
'
                Me.Close False
            End If
        Case vbCancel
'           b) If I select Cancel then there's no action, the dialog box closes and the workbook remains open as if nothing had happened.
            Cancel = True  'simply cancel the closing of this workbook
        Case vbYes
'           c) If I select Yes, then the new data is saved, a second dialog box appears + vbconf with existing data overwritten. The file and Excel then close.

'           Bernie Dietrick
            Dim bdFileName      As String
            Dim FullFileName    As String
'
            Application.DisplayAlerts = False
'
            FullFileName = ActiveWorkbook.FullName
            bdFileName = Left(ActiveWorkbook.Name, Len(ActiveWorkbook.Name) - 5)
'
                ActiveWorkbook.SaveCopyAs Filename:="E:\BACKUPS\Exercise Log\" & bdFileName & " Backup - " & Format(Now, "dddd dd mmmm yyyy, h.mm am/pm") & ".xlsm"
'
'               Backup to 128gb USB drive as well
                ActiveWorkbook.SaveCopyAs Filename:="Y:\DOCUMENTS\EXERCISE LOG\Exit Backups\" & bdFileName & " Backup - " & Format(Now, "dddd dd mmmm yyyy, h.mm am/pm") & ".xlsm"
'
            ActiveWorkbook.SaveAs Filename:=FullFileName
'
            Application.DisplayAlerts = True
'
            Worksheets("Training Log").[H1:H9] = vbNullString
            Worksheets("Daily Tracking").[CI1:CZ1] = vbNullString
            Worksheets("Indoor Bike").[I1:I2] = vbNullString
'
            MsgBox "New backup files created in" & vbNewLine & vbNewLine & "E:\BACKUPS\EXERCISE LOG    " & vbNewLine & vbNewLine _
                & "Y:\DOCUMENTS\EXERCISE LOG\EXIT BACKUPS" & vbNewLine & vbNewLine & "Exercise Log will now close", vbInformation, "Master File Overwritten"
'
            ThisWorkbook.Saved = True
    End Select
'
'   Turn Settings back on
    Application.EnableEvents = True                                                             ' Turn EnableEvents back on
    Application.Calculation = xlCalculationAutomatic                                            ' Turn AutoCalculation back on
    Application.ScreenUpdating = True                                                           ' Turn Screen Updating back on
End Sub

I removed this line (and the End If) as it seemed unnecessary (it was someone else who was testing my workbook years ago) and it hasn't caused any problems after testing:
VBA Code:
If LCase(Application.UserName) <> "jsullivan" Then

Also, another idea, I wonder if you first save the workbook and then copy paste it to the 2 folders while renaming would be faster
Sure, I thought that was what's happening now? Clearly not :)

Edit - the first msgbox says "Are you sure you want to overwrite the master file?" I don't think this is necessary as well as the usual "do you want to save the file" msg - probably makes sense to remove that.
 
Last edited:

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
The first msgbox ("Are you sure you want to overwrite the master file?") is necessary for "Select Case" portion to function. The second one must be the default save prompt before closing a changed file.

Just performed some tests (I had a 40 MB file by luck). With the current code structure it took almost 7 seconds each save (21 seconds total, as you stateted in your first post).

Test1: 6639.4844
Test2: 6805.1094
Test3: 7017.9327

Then I first saved the master file and just copy pasted the master instead. Time saving is a lot :)

Test1: 8384.8405
Test2: 28.8495
Test1: 28.0661

Shall we adopt this method instead? I can modify the current code for it.
 

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
By the way this change is not even saved?! Maybe put them before file saving happens?

VBA Code:
            Worksheets("Training Log").[H1:H9] = vbNullString
            Worksheets("Daily Tracking").[CI1:CZ1] = vbNullString
            Worksheets("Indoor Bike").[I1:I2] = vbNullString
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
The second one must be the default save prompt before closing a changed file.
Strange it's appearing now as I'm 100% certain I didn't see that 3rd 'default' save msgbox until today - I previously only saw 2: the first "overwrite" one and then the backup one 21 secs (!) later.

Then I first saved the master file and just copy pasted the master instead. Time saving is a lot

Test1: 8384.8405
Test2: 28.8495
Test1: 28.0661
Err I can't understand that :) Are you saying it's 8.3 seconds, reduced from 28? Anyway, sure, let's go for it!

Worksheets("Training Log").[H1:H9] = vbNullString
Worksheets("Daily Tracking").[CI1:CZ1] = vbNullString
Worksheets("Indoor Bike").[I1:I2] = vbNullString
As you know, these 3 lines just clear the cell content in those ranges - what is there to save? But if it's not running then sure, put them before file saving.

Many thanks!
 

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I start a timer and debug.print elapsed times.

So, saving the master file is 7-8 seconds. Then copying the backups takes 28 milliseconds. Numbers are milliseconds.

As you know, these 3 lines just clear the cell content in those ranges - what is there to save? But if it's not running then sure, put them before file saving.
Yes, they are cleared but it is not saved anyway, since this is the workbook close event.

Ok, lets try it, hopefully you will like the outcome.
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
saving the master file is 7-8 seconds. Then copying the backups takes 28 milliseconds. Numbers are milliseconds.
:oops:

Does that mean that any content that the code is erasing will reappear when the workbook is opened again?

So I guess I'll now have 3 msgboxes before closing the workbook?

Edit: In short, all I want to do re saving when I close the workbook is:

1) either save the file or not
2) either backup to the 2 folders or not

Which should really only be a message box for each of those? I don't know what the purpose of the "overwrite" Msg vs the "Save" one is?
 
Last edited:

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Let's try this:

VBA Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)

    Dim MsgResult As Long

'   Turn Settings off
    Application.ScreenUpdating = False              ' Turn Screen Updating off
    Application.Calculation = xlCalculationManual   ' Turn AutoCalculation off
    Application.EnableEvents = False                ' Turn EnableEvents off
    
    MsgResult = MsgBox("Are you SURE you want to overwrite the master Exercise Log file?   ", vbYesNoCancel + vbExclamation, "WARNING")

    Select Case MsgResult
        Case vbNo
'           a) If you select No, you get a second dialog box saying "Existing file unchanged" and the workbook as well as Excel will close.
'
'           Checks to see if other workbooks are open.  It will not shut down the application if there are other workbooks open
            If Application.Workbooks.Count < 2 Then
                MsgBox "Master file unchanged - data NOT saved" & vbNewLine & "" & vbNewLine & "Exercise Log will now close", vbInformation, "Master File Unchanged "
'
'               The following line does not save the workbook but sets a bit that tells Excel that any changes have already been saved, even if the changes were not actually saved
'
                ThisWorkbook.Saved = True
'
                Application.Quit
            Else
'               Other workbooks are open.  Leave the application alone and simply close this workbook.
                MsgBox "Other workbooks open - data NOT saved!" & vbNewLine & "" & vbNewLine & "Exercise Log will now close!", vbInformation, "Master File Unchanged "
'
                Me.Close False
            End If
        Case vbCancel
'           b) If I select Cancel then there's no action, the dialog box closes and the workbook remains open as if nothing had happened.
            Cancel = True  'simply cancel the closing of this workbook
        Case vbYes
'           c) If I select Yes, then the new data is saved, a second dialog box appears + vbconf with existing data overwritten. The file and Excel then close.

'           Bernie Dietrick
            Dim FullFileName As String
            Dim bdFileName As String
            
            Application.DisplayAlerts = False
            
            Dim fso As Object
    
            Set fso = CreateObject("Scripting.FileSystemObject")
                        
            FullFileName = ThisWorkbook.FullName
            bdFileName = fso.GetBaseName(FullFileName)
            
'           Clear ranges
            Worksheets("Training Log").[H1:H9] = vbNullString
            Worksheets("Daily Tracking").[CI1:CZ1] = vbNullString
            Worksheets("Indoor Bike").[I1:I2] = vbNullString
            
'           Save the master file
            ThisWorkbook.Save
            
            Dim Path_Backup1 As String
            Dim Path_Backup2 As String
            
            Path_Backup1 = "E:\BACKUPS\Exercise Log\"
            Path_Backup2 = "Y:\DOCUMENTS\EXERCISE LOG\Exit Backups\"
            
'           Backup to E:\BACKUPS
            fso.CopyFile FullFileName, fso.BuildPath(Path_Backup1, bdFileName & " Backup - " & Format(Now, "dddd dd mmmm yyyy, h.mm am/pm") & ".xlsm")
            
'           Backup to 128GB USB Drive
            fso.CopyFile FullFileName, fso.BuildPath(Path_Backup2, bdFileName & " Backup - " & Format(Now, "dddd dd mmmm yyyy, h.mm am/pm") & ".xlsm")
            
            Application.DisplayAlerts = True
            
            MsgBox "New backup files created in" & vbNewLine & vbNewLine & Path_Backup1 & vbNewLine & vbNewLine _
                & Path_Backup2 & vbNewLine & vbNewLine & "Exercise Log will now close", vbInformation, "Master File Overwritten"
    End Select

'   Turn Settings back on
    Application.EnableEvents = True                                                             ' Turn EnableEvents back on
    Application.Calculation = xlCalculationAutomatic                                            ' Turn AutoCalculation back on
    Application.ScreenUpdating = True

End Sub
 
Solution

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
That's much faster now, thanks ever such a lot Gokhan!

I've read all your comments, thanks again - but I do find the final 'default' save msgbox irrelevant (and therefore annoying) because as I understand it, hasn't the file already been saved, regardless of if I say "yes" or "no"?

As an aside, I remember whenever I got any kind of VB error and I subsequently closed the workbook, I didn't see both of the message boxes before closing, so if the default save msgbox isn't doing anything then can that be suppressed/disabled?
 

Gokhan Aycan

Active Member
Joined
Aug 8, 2021
Messages
399
Office Version
  1. 365
Platform
  1. Windows
No, file is not saved regardless. When you are asked "Are you SURE you want to overwrite the master Exercise Log file?":

If Answer = No, then master is closed without saving. You should either see "Master file unchanged - data NOT saved" or "Other workbooks open - data NOT saved!" message boxes.
If Answer = Cancel, then no saving occurs and also file close is cancelled.
If Answer = Yes, then master is saved, and copies are made, and then master is closed.

You shouldn't be getting any other msgbox other than "New backup files created in ...".
 

Ironman

Well-known Member
Joined
Jan 31, 2004
Messages
858
Office Version
  1. 365
Platform
  1. Windows
Yes I agree, but I get the Excel msgbox right after that, just before the workbook actually closes...
 

Attachments

  • new backup created.png
    new backup created.png
    12.3 KB · Views: 5
  • Excel Save Changes.png
    Excel Save Changes.png
    6.8 KB · Views: 5

Forum statistics

Threads
1,147,957
Messages
5,744,042
Members
423,841
Latest member
barren

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top