locked
Excel crashes when user saves VSTO template with event handler RRS feed

  • Question

  • I'm working on an Excel 2003 VSTO SE Document-level project in which I'm adding an event handler to one of the sheets. In this case, when the user makes a change to the worksheet and attempts to save the workbook, Excel displays the message of death--"Microsoft Office Excel has encountered a problem and needs to close. We are sorry for the inconvenience."

    I've duplicated the error in a simplified project, using the wizard-created template. To Sheet1.cs I've added the following code:

    private void Sheet1_Startup(object sender, System.EventArgs e)  
    {  
        this.Change += new Microsoft.Office.Interop.Excel.DocEvents_ChangeEventHandler(Sheet1_Change);  
    }  
     
    void Sheet1_Change(Microsoft.Office.Interop.Excel.Range Target)  
    {  
        this.CircleInvalid();  

    Finding no other way to modify the template inside Visual Studio (The Excel menu doesn't seem to show in VS), I modified the template in Excel to add a couple of validation fields and a validation list, illustrated below. The two cells in the box are validation fields. The list of numbers to the right is the validation list. Since this isn't an Excel forum, I won't describe how to implement a validation field in Excel.

    1 1
    6 2
    3
    4
    5

    When the VSTO project is run in Visual Studio 2005 (Start without debugging), select the first cell and change it to any other value. Upon making the change, the Sheet1_Change handler calls CircleInvalid() and Excel draws a red circle around the cell containing the invalid value "6." Just what I want to happen.

    Now if the user tries to save the modified workbook using "Save As...," Excel displays an error message and invites the user to send Microsoft a report.

    When I comment out the line "this.CircleInvalid();" the application does not exhibit this error. In fact it doesn't do anything, as you'd expect.

    How can I call CircleInvalid() in this situation without having Excel crash? Do I have to disable the event handler when the user saves the workbook? Or is something else happening?
    Tuesday, February 3, 2009 5:29 PM

Answers

  • I think I've found the solution myself.

    The problem is not the CircleInvalid() method, it's the circles themselves. They have to be cleared using the ClearCircles() method before the sheet can be saved. I found that when I disabled the call to CircleInvalid in my code and manually clicked "CircleInvalid" on Excel's Formula audit toolbar and tried to save the workbook, Excel crashed. When I tried this in a non-VSTO Excel workbook, Excel appears to clear the circles automatically before saving.

    So in a VSTO application, you have to clear the circles explicitly before saving. Easily accomplished by adding a BeforeSave handler to the Workbook and calling the ClearCircles method for each worksheet.

    Tuesday, February 3, 2009 8:34 PM