How to automatically fire a macro after saving and after opening?

After the secure file has been saved I want the new filename and the saving date to be automatically written in the header of all the sheets of the still open new secure workbook. My sub to do this works fine anytime as I manually start the macro.

However my first problem is, that I need an event to fire the macro automatically shortly AFTER the saving process has been accomplished. My choice was the Private Sub Workbook_AfterSave(ByVal Success As Boolean) Event. After packing my code in such an automatically fired sub and packing the sub in the workbook the following happened:

In an uncoded file the saving-process is accomplished first and the macro code is performed afterwards. As intended.
To the padlock-coded file the following happens: After typing the saving button the macro code is performed first and the saving-process is only accomplished afterwards. This of course makes the Private Sub Workbook_AfterSave Event useless for a coded file.

What can I do to achieve with an encoded file, that a macro code is only started, AFTER the saving process has been accomplished?

I also want to use the Private Sub Workbook_Open() Event to write the headers each time after the coded workbook has been opened. My second problem is, that I can only include one event sub in the workbook. Either Workbook_open or workbook_AfterSave. What can I do?

For the workbook_Open module you have to move all the coding to
Microsoft Excel Object (above forms)
The last tab under sheets should be ThisWorkbook

Place all the required functions here as well as required workbook open macros

Your sub will be: "Sub Workbook_Open()

Not sure about after save macro’s

Thank you, I am aware of that. But still my two problems are not solved:

  • after save event in a coded file and
  • including two event fired macros (“after save” and “open”) in “Thisworkbook”.

XLS Padlock uses the AfterSave event to save the secure file. Unfortunately, you can’t tell Excel to change the event orders: your AfterSave code will be executed before XLS Padlock’s internal AfterSave code.

OK. For Problem 1 I obviously had to find an other solution: I use a polling procedure with Application.OnTime-Method.
Problem 2 is solved in the meantime: Two event fired macros (subs) can be integrated in the same module. And I have to use the module of “Thisworkbook”. Thanks.

1 Like