Information
The topic you requested is included in another documentation set. For convenience, it's displayed below. Choose Switch to see the topic in its original location.

Walkthrough: Programming Against Events of a NamedRange Control

Note Required applications

The features in this topic are available only if you have the required applications installed.

For more information, see Features Available by Product Combination.

  • One of these development environments:

    VSTO 2005

    -or-

    Visual Studio Team System

  • Microsoft Office 2003

This walkthrough demonstrates how to add a NamedRange control to a Microsoft Office Excel 2003 worksheet and program against its events using Microsoft Visual Studio 2005 Tools for the Microsoft Office System.

During this walkthrough, you will learn how to:

  • Add a NamedRange control to a worksheet.

  • Program against NamedRange control events.

  • Test your project.

NoteNote

The dialog boxes and menu commands you see might differ from those described in Help depending on your active settings or edition. To change your settings, choose Import and Export Settings on the Tools menu. For more information, see Visual Studio Settings.

To complete this walkthrough, you will need:

  • Visual Studio Tools for Office.

  • Microsoft Office Excel 2003.

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

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 Visual Studio Tools for Office Projects.

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

    Events:

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 Visual Studio Tools for Office.

    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:

    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;
    }
    
    
    NoteNote

    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 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);
    }
    
    
    NoteNote

    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 are some tasks that might come next:

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

Show:
© 2014 Microsoft