I'm trying to use Application.EnableCancelKey = xlDisabled so that when I save the workbook via VBA within the Before Close sub of the workbook module the user is not able to abort the save.
In my (large) production workbook:
In a smaller test workbook I put together with the same code:
What is the problem?
I'm worried that my production workbook is corrupt.
Though of course there is the question that the code even fails in the new test workbook I built - perhaps the size of the workbook influences how easily the code fails?
Can anyone help and spot any relevant errors in my code, or offer an opinion on whether my workbook could be corrupt (strange if the new test one fails too sometimes though) and how I may fix this?
Or any other solutions?!
I've put the example workbook on Google Drive - you should be able to just download it without even a Google account at https://docs.google.com/file/d/0B9P0...it?usp=sharing)
To replicate my issue:
My code is also below:
I would really appreciate any help or insight you can offer.
Many thanks,
David
In my (large) production workbook:
- This fails more often than not and pressing the 'esc' key a few times will cause the save to be aborted and the error message "Run-time error 1004: Document not saved". The only button I am able to click is 'End' (or help) - 'Continue' and 'Debug' are greyed out.
- Occasionally the code does work and disable the 'esc' key but the vast majority of times 'esc' functions with a few presses.
In a smaller test workbook I put together with the same code:
- This has some dummy random data across c.10 sheets (so that workbook takes some time to save) and some blank sheets with the same names as those referred to in my VBA code
- Though the code is identical, in this workbook the code works 90% of the time and while it is possible to make the document abort saving by pressing 'esc' a large amount of times, 'esc' has to be pressed much more often than in my production workbook and the code only fails c.10% of the time rather than around 80% of the time.
What is the problem?
I'm worried that my production workbook is corrupt.
Though of course there is the question that the code even fails in the new test workbook I built - perhaps the size of the workbook influences how easily the code fails?
Can anyone help and spot any relevant errors in my code, or offer an opinion on whether my workbook could be corrupt (strange if the new test one fails too sometimes though) and how I may fix this?
Or any other solutions?!
I've put the example workbook on Google Drive - you should be able to just download it without even a Google account at https://docs.google.com/file/d/0B9P0...it?usp=sharing)
To replicate my issue:
- The various passwords are 'admin'
- Go to any sheet and make any change
- Then press ctrl+F4 to close the workbook
- My code will run a dialog asking you if you want to save
- Say Yes
- Press 'esc' key many many times while the status bar shows the save operation in progress
- Around 10% of the time the save operation will be aborted and the error message above displayed
- The code seems more likely to fail if upon 'esc' not working, the sheet saving successfully and thus exiting leaving Excel open with no other workbooks, you then open the sheet again - for some reason the first instance of opening the workbook (with a brand new Excel application process) seems more reliable
My code is also below:
Code:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Dim ConfirmDesireSave
Dim PreventSavePwdAttempt1
Dim PreventSavePwdAttempt2
Dim PreventSavePwdAttempt3
Dim PreventSavePwd As String
PreventSavePwd = "admin"
'Go to system closing screen
Application.Goto (Sheets("Close System").Range("AM1"))
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
'Save file so that the security measures at the end of this code are held in place
ConfirmDesireSave = MsgBox("Do you wish to save changes to the system?" & vbNewLine & vbNewLine & "If you select No, you will require the Prevent Saving Password" & vbNewLine & vbNewLine & "Save? Select cancel to abort system closing", vbQuestion + vbYesNoCancel, "Save System Changes?")
If ConfirmDesireSave = vbCancel Then
Cancel = True
'Go to start screen
Application.Goto (Sheets("Start").Range("Z1"))
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
'Prevent asset register closing and 'enable macros' sheet loading as system not closing
End
Else
If ConfirmDesireSave = vbYes Then
Application.EnableCancelKey = xlDisabled
ActiveWorkbook.Save
Else
If ConfirmDesireSave = vbNo Then
PreventSavePwdAttempt1 = InputBox("To prevent saving changes to the system please enter the Prevent Saving Password" & vbNewLine & vbNewLine & "This is attempt 1 of 3", "Enter Password to Prevent System Saving Changes")
If PreventSavePwdAttempt1 = PreventSavePwd Then
Else
PreventSavePwdAttempt2 = InputBox("To prevent saving changes to the system please enter the Prevent Saving Password" & vbNewLine & vbNewLine & "This is attempt 2 of 3", "Enter Password to Prevent System Saving Changes")
If PreventSavePwdAttempt2 = PreventSavePwd Then
Else
PreventSavePwdAttempt3 = InputBox("To prevent saving changes to the system please enter the Prevent Saving Password" & vbNewLine & vbNewLine & "This is attempt 3 of 3" & vbNewLine & vbNewLine & "If you get the password wrong this time the system will not close", "Enter Password to Prevent System Saving Changes")
If PreventSavePwdAttempt3 = PreventSavePwd Then
Else
Cancel = True
'Go to start screen
Application.Goto (Sheets("Start").Range("Z1"))
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
'Prevent asset register closing and 'enable macros' sheet loading as system not closing
End
End If
End If
End If
End If
End If
End If
'Go to enable macros screen (for usability and in case Asset Register the active sheet)
Application.Goto (Sheets("Enable Macros").Range("AM1"))
ActiveWindow.ScrollColumn = 1
ActiveWindow.ScrollRow = 1
'Block access to Asset Register and remove from navigation lists
Sheets("Asset Register").Visible = xlSheetVeryHidden
'Remove log out buttons at start screen
Sheets("Start").Shapes("Start_AssetRegisterLogOut").Visible = False
'Re-enable escape key
'Application.EnableCancelKey = xlInterupt
'Mark workbook as saved to prevent system dialog requesting whether to save
ThisWorkbook.Saved = True
End SubMany thanks,
David
