Why does example Workbook_BeforeSave macro always save?

[andydtaylor]

Hi,

I'm looking for help implementing a worksheet_beforesave macro. If I run exactly what is written at the MS Excel help page below, my workbook saves even if the user clicks "No" to "Do you really want to save the workbook?". Has anyone seen this effect before and have an idea how this behavior can be corrected?

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, _
        Cancel As Boolean)
    a = MsgBox("Do you really want to save the workbook?", vbYesNo)
    If a = vbNo Then Cancel = True
End Sub

Workbook.BeforeSave event (Excel)

I've seen this behaviour as I come back to this workbook over the last few weeks so it's not a one-time effect. I'm using Office Professional Plus 2016. I appreciate your help!

My objective is to prevent a user from pressing ctrl-s to save a workbook without a filename check being run. I have already implemented this check as part of a collection of power query queries so the final check will reduce down to the following, albeit perhaps with a sheet reference:

If Range("o_val[Cross-Check]").Value = "Problem Detected" Then

In debugging this I realised that even the MS stock example fails, hence the focus of this post. For context in case there is another way to implement my goal.

Regards,

Andrew

 

[Anthony47]

Can you confirm that the Sub Workbook_BeforeSave is saved into the ThisWorkbook vba module?

 

[andydtaylor]

It is indeed saved in the ThisWorkbook module.

 

[RoryA]

I can't replicate that. If I press No, the save doesn't happen.

 

[Jaafar Tribak]

Do you have any code that maybe temporarily disabling the application events and saving the workbook ? Maybe in an addin or personal workbook.

 

[andydtaylor]

Yyyyeeessss. I think I have an explanation:

Secure Islands - Wikipedia

This add-in launches when you save a file and asks you if it is confidential. This must be interfering with my before save macro.

I'd hoped there was another explanation.

Thanks for helping!

 

[Jaafar Tribak]

I was thinking something along these lines where application events are hooked and therefore, even if you answer NO to saving, the workbook will always be saved behind the scenes.

In the ThisWorkbook Module:

Option Explicit

Private WithEvents xl As Application

Private Sub Workbook_Open()
    Set xl = Application
End Sub

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)
    Dim a As VbMsgBoxResult
    a = MsgBox("Do you really want to save the workbook?", vbYesNo)
    If a = vbNo Then Cancel = True
End Sub

Private Sub xl_WorkbookBeforeSave(ByVal Wb As Workbook, ByVal SaveAsUI As Boolean, Cancel As Boolean)
    If Wb Is Me Then
        Application.EnableEvents = False
        Wb.Save
        Application.EnableEvents = True
    End If
End Sub

Now, if the above is in an addin, you may not even notice.