Microsoft KB Archive/319341

Knowledge Base

How To Handle Events for the Office XP Spreadsheet Component on a Windows Form in Visual C# .NET

Article ID: 319341

Article Last Modified on 1/17/2007

---

APPLIES TO

• Microsoft Office Web Components
• Microsoft Visual C# .NET 2002 Standard Edition

---

This article was previously published under Q319341

For a Microsoft Visual Basic .NET version of this article, see 319342.

IN THIS TASK

SUMMARY

• Step-by-Step Guide

REFERENCES

SUMMARY

You can use this step-by-step guide with Visual C# .NET to handle events for an Office XP Spreadsheet component on a Windows Form.

back to the top

Step-by-Step Guide

Before you start the following steps, you must modify the class wrappers that Visual Studio .NET generates for the Office XP Web Components (OWC). You must modify the class wrappers for Visual Basic .NET to properly handle OWC events. For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

328275 How To Handle Events for the Office Web Components in Visual Studio .NET

1. Create a new Visual C# .NET Windows Application project. Name the project SheetEvents.
   
   Form1 creates, and then opens in Design view.
2. On the View menu, click Toolbox.
3. Drag the Spreadsheet component from the Toolbox to Form1.
4. On the View menu, click Code.

5. Add the following lines of code before the NAMESPACE statement in the Form1.cs file:

   using System.Diagnostics;
   using OWC10 = Microsoft.Office.Interop.OWC;
                       

6. Add the following code to the EndEdit, BeforeContextMenu, and CommandExecute events:

   private void BeforeContextMenu(object sender, 
   AxMicrosoft.Office.Interop.OWC.ISpreadsheetEventSink_BeforeContextMenuEvent e)
   {
       Debug.WriteLine("BeforeContextMenu Event: Create Custom Menu");
   
       // Build the menu structure:
       // Menu Item        Submenu Item
       // ==============   ============
       // - Format As...   - Blue
       //                  - Red
       // - Enter Date
       object[] oAction1 = new object[]{"&Blue","FormatAsBlue"};
       object[] oAction2 = new object[]{"&Red", "FormatAsRed"};
       object[] oAction3 = new Object[]{"&Green","FormatAsGreen"};
       object[] oSubMenu1 = new object[]{oAction1, oAction2, oAction3};
       object[] oMenu1 = new Object[]{"&Format As...", oSubMenu1};
       object[] oMenu2 = new object[]{"&Enter Date", "EnterDate"};
       object[] oMenu = new object[]{oMenu1, oMenu2};
       e.menu.Value=oMenu;
   
   }
   
   private void CommandExecute(object sender, 
   AxMicrosoft.Office.Interop.OWC.ISpreadsheetEventSink_CommandExecuteEvent e)
   {
       Debug.WriteLine("CommandExecute Event: Menu action = " + 
              e.command.ToString());
   
       OWC10.Range sel = axSpreadsheet1.Selection; 
       object oColor = null;
   
       // Take the action selected on the context menu.
       switch(e.command.ToString())
       {
           case "FormatAsRed": 
               oColor = "red";
               sel.Font.set_Color(ref oColor);
               break;
           case "FormatAsBlue":
               oColor = "blue";
               sel.Font.set_Color(ref oColor);
               break;
           case "FormatAsGreen":
               oColor = "green";
               sel.Font.set_Color(ref oColor);
               break;
           case "EnterDate":
               sel.Formula="=TODAY()";
               break;
       }           
   
   }
   
   private void EndEdit(object sender, 
   AxMicrosoft.Office.Interop.OWC.ISpreadsheetEventSink_EndEditEvent e)
   {
       Debug.Write("EndEdit Event: ");
   
       // Verify if the cell that is being edited is cell A1.
       object oOpt = System.Reflection.Missing.Value;
       string sAddr = axSpreadsheet1.ActiveCell.get_Address(
           ref oOpt, ref oOpt, OWC10.XlReferenceStyle.xlA1, ref oOpt, 
                   ref oOpt);
       if(sAddr!="$A$1")
       {
           Debug.WriteLine("Cell is Not A1, Allow edit");
           return;
       }
   
       // If it is cell A1, confirm that the value entered is a number 
       // between zero and 100.
       string sMsg = "Cell A1 must contain a number between 0 and 100.";
       string sCaption = "Spreadsheet10 Event Demo";
       try
       {
           double dVal = 
                      System.Double.Parse(e.finalValue.Value.ToString());
           if((dVal<0)||(dVal>100))
           {
              // Value not between 0 and 100.
                      Debug.WriteLine(
                      "Cell is A1 but value is not between 0 & 100. Cancel.");
              System.Windows.Forms.MessageBox.Show(sMsg, sCaption);
              e.cancel.Value=true; // Cancel the edit.
           }
           else
           {
              Debug.WriteLine(
                      "Cell is A1 and value is between 0 & 100. Allow edit.");
           }
       }
       catch (System.FormatException fe)
       {
           // Cannot convert to a double.
           Debug.WriteLine(
                   "Cell is A1 but the value is not a number. Cancel.");
           System.Windows.Forms.MessageBox.Show(sMsg, sCaption);
           e.cancel.Value=true;    // Cancel the edit.
       }
   
   }
   
                       

7. Press F5 to build and to run the sample.
8. Enter a value in cell A1.
   
   If the value is not a number between zero and 100, you receive a message, and then the edit cancels.
9. Right-click any cell to display the shortcut menu, and then click any of the commands on the menu so you can see the results.

back to the top

REFERENCES

For additional information, visit the following Microsoft Web site:

Microsoft Office Development with Visual Studio http://msdn2.microsoft.com/en-us/library/aa188489(office.10).aspx

back to the top

Additional query words: advise unadvise connectionpoint connection point trigger fired fires handler spread sheet owc owc10

Keywords: kbhowto kbhowtomaster kbofficewebspread KB319341

---

Send feedback to Microsoft

© Microsoft Corporation. All rights reserved.