I have a readonly Excel workbook containing a VBA application. The application saves any data that needs to be saved in a database, and the workbook is always closed without saving (by setting ThisWorkbook.Saved = True in BeforeClose).
I have the following problem:
User double-clicks on the workbook in Windows Explorer, workbook opens.
User double-clicks a second time on the workbook in Windows Explorer.
Excel prompts: "MyWorkbook.xls is already open. Reopening will cause any changes you made to be discarded. Do you want to reopen MyWorkbook.xls?"
If the user clicks on "Yes", the workbook is reopened without executing the BeforeClose event handler of the instance that was already open.
This is a problem in my application, because it means that some important cleanup code in the BeforeClose event handler does not get executed.
Can anyone suggest a VBA solution to this. Which could be:
Suppressing the prompt to reopen the workbook. Instead silently use the already-open instance.
Somehow get the BeforeClose or some other event handler to run in the original instance before it is closed, so I can run my cleanup code.
This is Excel 2003.
I can get rid of the unwanted prompt by setting "ThisWorkbook.Saved = True" in the Workbook_SheetChanged event handler (the VBA app is responsible for saving any data that needs to be saved in a database, so I don't care about having Excel saving changes).
However this doesn't solve my problem: if I do this, then double-clicking on the Workbook in explorer silently reopens the workbook, but still does this without calling by "BeforeClose" event handler.
So to rephrase the question:
- Is there anyway using VBA to detect and intercept a workbook being reopened in this way?
Accepting BKimmel's answer – it does seem there is no VBA way to intercept this event from within the workbook.
The solution I'll implement will be to move the application code into an XLA add-in, which is automatically loaded (if not already loaded) when the workbook is loaded. The add-in can handle Open and BeforeClose events, and store the information it needs to do the clean up.