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. 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.

System_CAPS_ICON_note.jpg Note

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 Customizing Development Settings in Visual Studio.

You need the following components to complete this walkthrough:

In this step, you will create an Excel workbook project using Visual Studio.

To create a new project

  1. 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

  1. Verify that the My Named Range Events.xlsx workbook is open in the Visual Studio designer, with Sheet1 displayed.

  2. From the Excel Controls tab of the Toolbox, drag a NamedRange control to cell A1 in Sheet1.

    The Add NamedRange Control dialog box appears.

  3. 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.

  4. Click OK.

    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.

  5. Add another NamedRange control to cell B3.

  6. 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.

  7. Click OK.

    Cell B3 becomes a range named namedRange2.

To add text to your worksheet

  1. In Cell A1, type the following text:

    This is an example of a NamedRange control.

  2. 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

  1. In Solution Explorer, right-click Sheet1.vb or Sheet1.cs and select View Code.

  2. Add code so the namedRange1_BeforeDoubleClick event handler looks like the following:

            private void namedRange1_BeforeDoubleClick(
                Microsoft.Office.Interop.Excel.Range Target, ref bool Cancel)
                this.namedRange2.Value2 = "The BeforeDoubleClick event occurred.";
                this.namedRange2.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Red);
                this.namedRange2.Font.Italic = true;

  3. In C#, you must add event handlers for the named range as shown in the Startup event below. For information on creating event handlers, see How to: Create Event Handlers in Office Projects.

                this.namedRange1.BeforeDoubleClick += new Microsoft.Office.Interop.Excel.
                this.namedRange1.Change += new Microsoft.Office.Interop.Excel.
                this.namedRange1.SelectionChange +=new Microsoft.Office.Interop.Excel.

To insert text into namedRange2 based on the Change event

  1. Add code so the NamedRange1_Change event handler looks like the following:

            private void namedRange1_Change(Microsoft.Office.Interop.Excel.Range Target)
                this.namedRange2.Value2 = "The Change event occurred.";
                this.namedRange2.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue);
                this.namedRange2.Font.Italic = false;

    System_CAPS_ICON_note.jpg Note

    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

  1. Add code so the NamedRange1_SelectionChange event handler looks like the following:

            private void namedRange1_SelectionChange(Microsoft.Office.Interop.Excel.Range Target)
                this.namedRange2.Value2 = "The SelectionChange event occurred.";
                this.namedRange2.AddComment("SelectionChange always occurs before BeforeDoubleClick.");
                this.namedRange2.Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Black);

    System_CAPS_ICON_note.jpg Note

    Because double-clicking a cell in an Excel range causes the selection to move into the range, a SelectionChange event occurs before the BeforeDoubleClick event occurs.

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

  1. Press F5 to run your project.

  2. 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.

  3. Double click inside namedRange1, and verify that the text regarding BeforeDoubleClick events is inserted with red italicized text in namedRange2.

  4. Click outside of namedRange1 and note that the change event occurs when exiting edit mode even though no change to the text was made.

  5. Change the text within namedRange1.

  6. Click outside of namedRange1, and verify that the text regarding Change event is inserted with blue text into namedRange2.

This walkthrough shows the basics of programming against events of a NamedRange control. Here is a task that might come next:

Host Items and Host Controls Overview
Automating Excel by Using Extended Objects
NamedRange Control
How to: Resize NamedRange Controls
How to: Add NamedRange Controls to Worksheets
Programmatic Limitations of Host Items and Host Controls
How to: Create Event Handlers in Office Projects