How to effectively use WorkbookBeforeClose event correctly?
- by Ahmad
On a daily basis, a person needs to check that specific workbooks have been correctly updated with Bloomberg and Reuters market data ie. all data has pulled through and that the 'numbers look correct'. In the past, people were not checking the 'numbers' which led to inaccurate uploads to other systems etc.
The idea is that 'something' needs to be developed to prevent the use from closing/saving the workbook unless he/she has checked that the updates are correct/accurate. The numbers look correct action is purely an intuitive exercise, thus will not be coded in any way.
The simple solution was to prompt users prior to closing the specific workbook to verify that the data has been checked.
Using VSTO SE for Excel 2007, an Add-in was created which hooks into the WorkbookBeforeClose event which is initialised in the add-in ThisAddIn_Startup
private void wb_BeforeClose(Xl.Workbook wb, ref bool cancel)
{
//.... snip ...
if (list.Contains(wb.Name))
{
DailogResult result = MessageBox.Show("some message", "sometitle", MessageBoxButtons.YesNo);
if (result != DialogResult.Yes)
{
cancel = true; // i think this prevents the whole application from closing
}
}
}
I have found the following ThisApplication.WorkbookBeforeSave vs ThisWorkbook.Application.WorkbookBeforeSave which recommends that one should use the ThisApplication.WorkbookBeforeClose event which I think is what I am doing since will span all files opened.
The issue I have with the approach is that assuming that I have several files open, some of which are in my list, the event prevents Excel from closing all files sequentially. It now requires each file to be closed individually.
Am I using the event correctly and is this effective & efficient use of the event?
Should I use the Application level event or document level event?
Is there a way to prevent the above behaviour?
Any other suggestions are welcomed
VS 2005 with VSTO SE
