Microsoft KB Archive/319342

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

Article ID: 319342

Article Last Modified on 1/17/2007

-

APPLIES TO


 * Microsoft Office Web Components
 * Microsoft Visual Basic .NET 2002 Standard Edition
 * Microsoft Visual Basic .NET 2003 Standard Edition

-



This article was previously published under Q319342



For a Microsoft Visual C# .NET version of this article, see 319341.



For a Microsoft Visual Basic 6.0 version of this article, see 235883.

IN THIS TASK
SUMMARY
 * Step-by-Step Guide

REFERENCES



SUMMARY
You can use this step-by-step guide to learn how Visual Basic .NET handles 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). Modification of the class wrappers is required 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

 Create a new Visual Basic Windows Application project. Name the project SheetEvents.

Form1 creates, and then opens in Design view. On the View menu, click Toolbox. Drag the Spreadsheet component from the Toolbox to Form1. On the View menu, click Code.  Add the following code at the top of Form1.vb: Imports OWC10 = Microsoft.Office.Interop.OWC   Add the following code to the EndEdit, BeforeContextMenu, and CommandExecute events: Private Sub AxSpreadsheet1_BeforeContextMenu( _ ByVal sender As Object, ByVal e As _ AxMicrosoft.Office.Interop.OWC.ISpreadsheetEventSink_BeforeContextMenuEvent) _ Handles AxSpreadsheet1.BeforeContextMenu Debug.WriteLine(&quot;BeforeContextMenu Event: Create Custom Menu&quot;) ' Build the menu structure: ' Menu Item       Submenu Item ' ==============  ============    ' - Format As... - Blue '                 - Red ' - Enter Date Dim oAction1 As Object = New Object {&quot;&Blue&quot;, &quot;FormatAsBlue&quot;} Dim oAction2 As Object = New Object {&quot;&Red&quot;, &quot;FormatAsRed&quot;} Dim oAction3 As Object = New Object {&quot;&Green&quot;, &quot;FormatAsGreen&quot;} Dim oSubMenu1 As Object = New Object {oAction1, oAction2, oAction3} Dim oMenu1 As Object = New Object {&quot;&Format As...&quot;, oSubMenu1} Dim oMenu2 As Object = New Object {&quot;&Enter Date&quot;, &quot;EnterDate&quot;} Dim oMenu As Object = New Object {oMenu1, oMenu2} e.menu.Value = oMenu End Sub Private Sub AxSpreadsheet1_CommandExecute( _ ByVal sender As Object, ByVal e As _ AxMicrosoft.Office.Interop.OWC.ISpreadsheetEventSink_CommandExecuteEvent) _ Handles AxSpreadsheet1.CommandExecute Debug.WriteLine(&quot;CommandExecute Event: Menu action = &quot; & _      e.command.ToString) Dim sel As OWC10.Range = AxSpreadsheet1.Selection ' Take the action selected on the shortcut menu. Select Case e.command.ToString Case &quot;FormatAsRed&quot; sel.Font.Color = &quot;red&quot; Case &quot;FormatAsBlue&quot; sel.Font.Color = &quot;blue&quot; Case &quot;FormatAsGreen&quot; sel.Font.Color = &quot;green&quot; Case &quot;EnterDate&quot; sel.Formula = &quot;=TODAY&quot; End Select End Sub Private Sub AxSpreadsheet1_EndEdit( _ ByVal sender As Object, ByVal e As _  AxMicrosoft.Office.Interop.OWC.ISpreadsheetEventSink_EndEditEvent) _ Handles AxSpreadsheet1.EndEdit Debug.Write(&quot;EndEdit Event: &quot;) ' Verify if the cell that is being edited is cell A1. If AxSpreadsheet1.ActiveCell.Address <> &quot;$A$1&quot; Then Debug.WriteLine(&quot;Cell is Not A1, Allow edit&quot;) Exit Sub End If   ' If it is cell A1, confirm that the value entered is a number ' between 0 and 100. Dim sMsg As String = &quot;Cell A1 must contain a number between 0 and 100.&quot; Dim sCaption As String = &quot;Spreadsheet10 Event Demo&quot; Try Dim dVal As Double dVal = System.Double.Parse(e.finalValue.Value.ToString) If (dVal < 0) Or (dVal > 100) Then ' Value not between 0 and 100. Debug.WriteLine(&quot;Cell is A1 but the value is not &quot; & _              &quot;between 0 and 100, Cancel edit.&quot;) System.Windows.Forms.MessageBox.Show(sMsg, sCaption) e.cancel.Value = True 'Cancel the edit Else Debug.WriteLine(&quot;Cell is A1 and value is between &quot; & _              &quot;0 and 100, Allow edit.&quot;) End If   Catch ' Cannot convert to a double. Debug.WriteLine(&quot;Cell is A1 but the value is not &quot; & _          &quot;a number, Cancel edit.&quot;) System.Windows.Forms.MessageBox.Show(sMsg, sCaption) e.cancel.Value = True 'Cancel the edit End Try End Sub </li> Press F5 to build and to run the sample.</li> 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 is canceled.</li> Right-click any cell to display the shortcut menu, select any one of the commands on the menu, and then notice the results.</li></ol>

back to the top

<div class="references_section">