Excel VBA Read Only supersedes BeforeSave

Question

I have code that incorporates business logic for saving documents (Excel 365) to ensure proper naming convention, file locations etc etc as a Sub Workbook_BeforeSave

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
Cancel = True  ''Cancels the Save from the button push or Ctrl+S
Application.EnableEvents = False
'' code code code
Application.EnableEvents = True
End Sub

The problem is that if the file is opened as Read-Only (as most will be) Excel will prompt that "the file is Read-Only" (pic a) and go to the Save As screen in the File Ribbon (pic b). The Workbook_BeforeSave sub won't kick in until the SAVE button is pressed. It also won't move off this screen even after the sub has run. Is there any way to either:

Get in front of the Read-Only prompt ... or
Write some code to move off the Save As screen?

MS Read-Only promt (pic a) Save As Screen (pic b)

Huge thanks in advance!

Ricardo A Ricardo A · Accepted Answer · 2019-02-20T22:14:20

This is not a perfect approach but try this. This will cancel the save completely even after pressing the Save button, you can add your own save code.

Edit: Just realized that the code below does not stop the Read Only Alert, but when you click Save it cancels the Save and Closes the Save As Menu. However, when they use Ctrl+S the Send Keys {ESC} triggers a Ctrl+ESC which opens the Star Menu...

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    'Disable Read Only Alert
    Application.DisplayAlerts = False
    'Disable Events
    Application.EnableEvents = False
    'Cancel Save
    Cancel = True
    'Do whatever code you need in here
    '
    'Mark Workbook as Saved, allowing for the file to close without an alert even if not saved
    ThisWorkbook.Saved = True
    'Send Escape Key to leave Save As Menu
    Application.SendKeys "{ESC}", 1
    'Enable Events
    Application.EnableEvents = True
    'Enable Alerts
    Application.DisplayAlerts = True
End Sub

Excel VBA Read Only supersedes BeforeSave

1 Answers