Cascaded Open / Close / Save Not Working

Advertisement

I have a 'Summary' workbook which collates values from a series of 'sub-workbooks', (and can also update values in those sub workbooks).
the subworkbooks are setup so that when they are saved they also copy certain values out to another 'backup' file.
So there are 2 possible routes that the files will be used in:

1) SubWorkbook opened directly
- User opens SubWorkbook and makes some changes.
- User closes the SubWorkbook saving changes, or clicks the save button. The BeforeSave event opens the "restore" workbook, copies over the key values from the subworkbook, then saves & closes the "restore" workbook.

2) SubWorkbook opened remotely
- User / Admin opens the "Summary" workbook and changes an option.
- The summary workbook opens a subworkbook and makes changes, then saves it. The subworkbook should (as before), then open the restore workbook and save the key values etc.

The problem is in the second scenario the 'restore' workbook doesn't get opened.

I have created a set of 3 workbooks to illustrate the problem here.
Book1.xls = Summary workbook
Book2.xls = Subworkbook
Book3.xls = Restore workbook.

If you open book2 and then save it the 'BeforeSave' event will cause it to open book3 and write the output of 'Now()' to the next free row of book3-columnA, before then closing and saving book3,......