How to effectively use WorkbookBeforeClose event correctly?

On a daily basis, a person needs to check that specific workbooks have been correctly updated with Bloomberg and Reuters market data; i.e. 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.

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. Edit: this happens when Exit Excel is used from File menu.

Questions

1. Am I using the WorkbookBeforeClose event correctly and is this effective & efficient use of the event?
2. Should I use the Application level event or document level event?
3. Is the behaviour described above normal?
4. Any other suggestions are welcomed when using workbook events in an add-in

Update [30-Mar-2010]:

Tinkering around, I also tried the following which attempted to bind the BeforeClose event handler to every workbook that was opened as suggested from the link above.

private void ThisAddIn_Startup(...)
{
    // snip
    Globals.ThisAddin.Application.WorkbookOpen += Application_Open; 
}

private void Application_Open(XL.Workbook wb)
{
    wb.BeforeClose += Document_WorkbookBeforeClose; // method does the same as above
}

The problem that I found with this approach is that is I try to close to all Excel Files (using the Exit Excel option) the event handler does not execute. From my observation, this happens when the document to be checked is not the active document.

This method seems erratic when compared to my initial approach. The one thing I am not certain about or feel comfortable with is binding the event every time a document is opened.

Update [07-Apr-2010]:

Glen's suggested answer is useful but does not tackle the immediate questions at hand, I have thus clarified the last question a bit further.

I have also found this blog How to Get an Excel VSTO Workbook Closed Event which is somewhat relevant to my issue as it could be 开发者_如何转开发used within an alternative approach to my solution using a monitor-type approach to handling the workbooks (and possibly also use the newly introduced OnWorkbookClosed event).

Update [08-Apr-2010]:

There seems to be some confusion, I am not concerned about any validation on the workbooks themselves but rather whether the method I am using (i.e. using the Application-level WorkbookBeforeClose event) is correct. @Mathias' comment below shows the correct understanding of part of the problem in relation to question 3, I think that this is default excel behaviour though. The solution to overcome this was to create a close function that closes only my specific files.

3. Is the behaviour described above normal? Yes, but why? Because the add-in hooks into the application-level event, the checks and cancellation of the event blocks the application from closing any further workbooks. The key here is the ref bool cancel argument (cancel=false allows normal closing of the workbook(default), cancel=true prevents the workbook from closing)

VS 2005 with VSTO SE

The application-level WorkbookBeforeClose is the one to use. The issue is that erratic behaviour is resulting from files not being saved when WorkbookBeforeClose is called. If they are not, the Save event will kick and you've effectively now lost the WorkbookBeforeClose event because it has already occured. Here's some VBA code that helps handle this.

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    If Not Me.Saved Then
        NotSavedPrompt = Me.Name & " has not been saved. Would you like to save now?"
        SaveYesNo = MsgBox(NotSavedPrompt, vbQuestion + vbYesNoCancel)
        Select Case SaveYesNo
            Case vbYes
                Me.Save
            Case vbNo
                Me.Saved = True
            Case vbCancel
                Cancel = True
                Exit Sub
          End Select
    End If
    Call MyRoutine() //'this should be your sub that does what you want
End Sub

Since you are running into problems with this approach, and Excel may not let you do what you need, maybe you could consider another approach to solve the overall problem.

Could you build something into to the workbook that flags it as "Verified/Unverified", based on whether someone has verified the numbers? Depending on how the workbook is actually used (not clear from the question), the way to show it as unverified could vary... (visual) set the background color to a light shade of red, or (programmatic) change the used named range to point to a blank area so that other addins/macros won't find the data.

A user who is to verify the workbook would need to click a button (in the workbook or in a toolbar) to indicate that they have verified it. Your code would then undo the above changes and set a flag indicating that the workbook has been verified. This could be in a hidden cell somewhere in the book, or in the properties of the VBA module.

To recap... on open, check the flag. If not set, change the workbook to look unverified. If it is set, then no changes needed. When the user verifies the workbook, set the flag and save it permanently.

Hope this helps!

1. Am I using the WorkbookBeforeClose event correctly and is this effective & efficient use of the event?

   I cant find any better event to use. However, I think a another approach would be to have Save&Close function that uses the WorkbookBeforeSave event

2. Should I use the Application level event or document level event?

   At the time of writing this, I would say yes. Since I am not able to created a document-level add-in with the tools I have, this is the best solution available. Had the tools been available, I would have modified the _AssemblyName and _AssemblyLocation document properties of the relevant files. However, I would have changed the solution strategy by using using templates and then saving the files as required. In addition prior to saving, having not explored this fully, to add/remove the correct document properties. The document-level event would have well suited using the approach described above.

3. Is the behaviour described above normal?

   Yes, but why? Because the add-in hooks into the application-level event, the checks and cancellation of the event blocks the application from closing any further workbooks. The key here is the ref bool cancel argument (cancel=false allows normal closing of the workbook(default), cancel=true prevents the workbook from closing). If I am wrong here, please let me know.

4. Any other suggestions are welcomed when using workbook events in an add-in

   See above answers for alternative approaches and use of different events.