VBA Before Save Event

[jfr92]

I use Excel 2010 64 bit W7, although I doubt that is important here. All the message boards say that a save by VBA should trigger a Before_Save event macro in the workbook being saved. It does not for me. I remember reading in an MSDN document on a related issue the offhand comment that Oh, by the way, a VBA Close will NOT trigger the Before_Close event but you can force code in the subject workbook related to any event trigger to be executed with a command that is particular to that event. I cannot remember the command nor find that same Microsoft document I refer to.

 

[deanst]

Hi - I have the same setup as you and the Before_Save event triggers for me. I just put the following test code into a blank workbook:

In Module:

Sub test()


ActiveWorkbook.Save


End Sub

Then in the ThisWorkbook object put the function:

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


Debug.Print ("Triggered")


End Sub

Each time I run the test() macro I see the triggered getting printed in the immediate window showing it is being called.

Does this work for you?

 

[jfr92]

I guess I wasn't clear. The event trigger in book a does work for me when book a is saved manually or from vba code in book a but when book a is opened and modified and then saved by book b, the before save code in a is not executed. That's why I am looking for the vba instruction that can be put in b that tells event code in a to execute before b does the save. As I said, I saw a reference to such a command when I didn't need it and now can't find it again.

 

[deanst]

Ok - I think I understand what you are trying to do.

You have a workbook (call it Workbook A) with some code in which opens another workbook (call it Workbook B). The code in Workbook A makes some edits to Workbook B and then you save. On the save of Workbook B which was initiated by code in Workbook A you want a Before_Save event to trigger.

If I have understood that right, I think you can do the following:

In Workbook A within the "ThisWorkbook" object (or any other object, just not an ordinary module), place the following code:

Dim WithEvents wb As Workbook


Sub test()


Set wb = Workbooks.Open("Book2.xlsx")
wb.SaveAs


wb.Close


End Sub


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


Debug.Print ("Triggered on Save")


End Sub

Does that work for you?

Dean.