Walkthrough: Programming Against Events of a NamedRange Control
This walkthrough demonstrates how to add a NamedRange control to a Microsoft Office Excel worksheet and program against its events using by using Office development tools in Visual Studio.
Applies to: The information in this topic applies to document-level projects for Excel 2007 and Excel 2010. For more information, see Features Available by Office Application and Project Type.
During this walkthrough, you will learn how to:
Add a NamedRange control to a worksheet.
Program against NamedRange control events.
Test your project.
Your computer might show different names or locations for some of the Visual Studio user interface elements in the following instructions. The Visual Studio edition that you have and the settings that you use determine these elements. For more information, see Working with Settings.
You need the following components to complete this walkthrough:
An edition of Visual Studio 2010 that includes the Microsoft Office developer tools. For more information, see Configuring a Computer to Develop Office Solutions.
Excel 2007 or Excel 2010.
In this step, you will create an Excel workbook project using Visual Studio.
To create a new project
Create an Excel Workbook project with the name My Named Range Events. Make sure that Create a new document is selected. For more information, see How to: Create Office Projects in Visual Studio.
Visual Studio opens the new Excel workbook in the designer and adds the My Named Range Events project to Solution Explorer.
Because host controls are extended Office objects, you can add them to your document in the same manner you would add the native object. For example, you can add an Excel NamedRange control to a worksheet by opening the Insert menu, pointing to Name, and choosing Define. You can also add a NamedRange control by dragging it from the Toolbox onto the worksheet.
In this step, you will add two named range controls to the worksheet using the Toolbox, and then add text to the worksheet.
To add a range to your worksheet
Verify that the My Named Range Events.xls workbook is open in the Visual Studio designer, with Sheet1 displayed.
From the Excel Controls tab of the Toolbox, drag a NamedRange control to cell A1 in Sheet1.
The Add NamedRange Control dialog box appears.
Verify that $A$1 appears in the editable text box, and that cell A1 is selected. If it is not, click cell A1 to select it.
Cell A1 becomes a range named namedRange1. There is no visible indication on the worksheet, but namedRange1 appears in the Name box (located just above the worksheet on the left side) when cell A1 is selected.
Add another NamedRange control to cell B3.
Verify that $B$3 appears in the editable text box, and that cell B3 is selected. If it is not, click cell B3 to select it.
Cell B3 becomes a range named namedRange2.
To add text to your worksheet
In Cell A1, type the following text:
This is an example of a NamedRange control.
In Cell A3 (to the left of namedRange2), type the following text:
In the following sections, you will write code that inserts text into namedRange2 and modifies properties of the namedRange2 control in response to the BeforeDoubleClick, Change, and SelectionChange events of namedRange1.
To insert text into NamedRange2 based on the BeforeDoubleClick event
In Solution Explorer, right-click Sheet1.vb or Sheet1.cs and select View Code.
Add code so the namedRange1_BeforeDoubleClick event handler looks like the following:
Private Sub NamedRange1_BeforeDoubleClick( _ ByVal Target As Microsoft.Office.Interop.Excel.Range, ByRef Cancel As Boolean) _ Handles NamedRange1.BeforeDoubleClick With Me.NamedRange2 .Value2 = "The BeforeDoubleClick event occurred." .Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red) .Font.Italic = True End With End Sub
this.namedRange1.BeforeDoubleClick += new Microsoft.Office.Interop.Excel. DocEvents_BeforeDoubleClickEventHandler(namedRange1_BeforeDoubleClick); this.namedRange1.Change += new Microsoft.Office.Interop.Excel. DocEvents_ChangeEventHandler(namedRange1_Change); this.namedRange1.SelectionChange +=new Microsoft.Office.Interop.Excel. DocEvents_SelectionChangeEventHandler(namedRange1_SelectionChange);
To insert text into namedRange2 based on the Change event
Add code so the NamedRange1_Change event handler looks like the following:
Because double-clicking a cell in an Excel range enters edit mode, a Change event occurs when the selection is moved outside of the range even if no changes to text occurred.
To insert text into namedRange2 based on the SelectionChange event
Add code so the NamedRange1_SelectionChange event handler looks like the following:
Private Sub NamedRange1_SelectionChange(ByVal Target As Microsoft.Office.Interop.Excel.Range) _ Handles NamedRange1.SelectionChange With Me.NamedRange2 .Value2 = "The SelectionChange event occurred." .AddComment("SelectionChange always occurs before BeforeDoubleClick.") .Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black) End With End Sub
Now you can test your workbook to verify that text describing the events of a NamedRange control is inserted into another named range when the events are raised.
To test your document
Press F5 to run your project.
Place your cursor in namedRange1, and verify that the text regarding the SelectionChange event is inserted and that a comment is inserted into the worksheet.
Double click inside namedRange1, and verify that the text regarding BeforeDoubleClick events is inserted with red italicized text in namedRange2.
Click outside of namedRange1 and note that the change event occurs when exiting edit mode even though no change to the text was made.
Change the text within namedRange1.
Click outside of namedRange1, and verify that the text regarding Change event is inserted with blue text into namedRange2.