Code Execution Has Been Interrupted with Save Prevention Macro

Question

I've used a similar method to this answer to force users to use a certain button to save. I have the following:

public variable

Dim MacroSave As Boolean

button event

Sub RealSave_Click()
MacroSave = True
ThisWorkbook.Save
MacroSave = False
End Sub

save catcher

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not MacroSave Then
    Cancel = True 'Cancels any request to save the file
    MsgBox "Workbook not saved."
    Else
    Ret = MsgBox("Are you sure you want to save?", vbCritical Or vbYesNo, "Save File?")
        If Ret = vbNo Then Cancel = True
    End If
End Sub

sadness

The above code worked perfectly for some amount of time, but now it's telling me Code execution has been interrupted at MacroSave = False. If I hit Continue on the error pop-up, the file has saved fine. I can't see anything not working properly; it's just this pesky pop-up.

I tried

adding DoEvents before MacroSave = False (and after ThisWorkbook.Save), but the code was interrupted on DoEvents.
moving that line to the "save catcher", but the same error came up on the End Sub for the button event.
On Error Resume Next, which does not eliminate this issue.

What am I doing wrong and how can I eliminate this?

I'm wondering if you have some sort of race condition because the workbook is taking time to save and so you move to the last line of `RealSave_Click` before the `Workbook_BeforeSave` finishes. What if you declare `MacroSave = false` where it is dimensioned and eliminate `MacroSave = false` in `RealSave_Click`? — Matt Cremeens, Aug 13 '15 at 17:07
@MattCremeens I agree; that definitely seems like the case. A couple problems with your suggestion: (1) I've moved around the `MacroSave = False`, but the code is interrupted on the `End Sub` of `RealSave_Click()`. (2) I have to reset `MacroSave` to False at some point. (3) I believe assigning a value to `MacroSave` outside of a sub or function would be invalid. — ZX9, Aug 13 '15 at 17:14
Since you made it public, you could declare it in a workbook open event. — Matt Cremeens, Aug 13 '15 at 17:17
I am assuming up to you have something like `Option Explicit Dim MacroSave as Boolean`. — Matt Cremeens, Aug 13 '15 at 17:19
@MattCremeens Oh, ok; I apologize. I would like to be able to potentially save multiple times while it's open, though... — ZX9, Aug 13 '15 at 17:21
Did you use `Option Explicit` at the top of your code where you declared `MacroSave`? — Matt Cremeens, Aug 13 '15 at 17:22

score 0 · Answer 1 · answered Aug 13 '15 at 17:18

This works for me...

Regular module:

Option Explicit

Public MacroSave As Boolean

Sub RealSave_Click()

    If MsgBox("Are you sure you want to save?", vbExclamation + vbYesNo, _
             "Save File?") <> vbYes Then Exit Sub

    On Error GoTo haveError
    MacroSave = True
    ThisWorkbook.Save
    MsgBox "Saved!"

haveError:
    MacroSave = False

End Sub

ThisWorkbook module:

Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Not MacroSave Then
        Cancel = True
        MsgBox "Workbook not saved: please use the 'Save changes'" & _
               " button to save this workbook"
    End If
End Sub

score 0 · Accepted Answer · edited May 23 '17 at 12:29

0

The other suggestions are great, but...it boils down to this:

Press "Debug" button in the popup.
Press Ctrl+Pause|Break twice.
Hit the play button to continue.
Save the file after completion.

edited May 23 '17 at 12:29

Community

1
1

answered Aug 13 '15 at 17:39

ZX9

898
2
16
34