Beforesave and Aftersave ignored when saving as attachment

user_12234542 0
Viewed 125

I have some code that I have put in Workbook_AfterSave and Workbook_BeforeSave. It works great and does exactly what I want; however, during testing, I found an odd situation where Excel doesn't call these two functions when saving a document.

If I have a document open and select file->share->email->send as attachment, then Excel saves my document and puts it into an email message without ever calling Workbook_AfterSave and Workbook_BeforeSave (I've verified this by adding a msgbox popup within Workbook_AfterSave and Workbook_BeforeSave and it definitely doesn't popup when using the send as attachment within Excel).

So, I'm digging around with Google trying to find out what other options I might have to fix my loophole.

Background: My code in Workbook_AfterSave and Workbook_BeforeSave hides tabs, very hidden, where the user cannot see them at all. The user can hack the workbook, and see the hidden tabs, by using this email as attachment "feature".

Simplification of problem: Create a new workbook with no VBA Code in it an save as an .xlsm file. Add the following code:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

   MsgBox "Before Save"

End Sub

Select file->share->email->send as attachment. Note you get no message box; however, if you made any changes they were most certainly saved =).

vba excel
1 Answers

I have a theory about what's happening, but no real evidence.

When you open an workbook, Excel makes a copy in a Temp directory. All the changes you make are made to the temp copy. When you save, it replaces the file you opened with the temp copy thereby "writing" your changes to disk. If you close without saving, it simply deletes the temp copy.

When you send as an attachment, unsaved changes are included in the attachment. But the workbook is not "saved" at that point because if you try to close it, Excel will prompt you to save your changes.

What if, when you Save and Send - Send as Attachment, Excel simply attaches the temp copy and names the attachment the same as the opened workbook. In fact, since you can attach a workbook that's never been saved before, it must be using the temp copy.

That's my theory on why it's happening the way it is. What to do about it? I'm not sure. I think I would figure out how to hijack the Send as Attachment function in the Ribbon's Backstage to force a save or to simply not allow this workbook to be sent without saving.

If you decide to go that route, search for Excel Ribbon File Backstage and you should find some instructions on how to modify that particular function.

dkusleika_42800
Related