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

Prerequisites

You need the following components to complete this walkthrough:

• An edition of Visual Studio 2013 that includes the Microsoft Office developer tools. For more information, see Configuring a Computer to Develop Office Solutions.

• Excel 2013 or Excel 2010.

Creating the Project

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.

Adding Text and Named Ranges to the Worksheet

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:

   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.

Adding Code to Respond to the BeforeDoubleClick Event

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

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

Adding Code to Respond to the Change Event

To insert text into namedRange2 based on the Change event

• Add code so the NamedRange1_Change event handler looks like the following:

  Private Sub NamedRange1_Change(ByVal Target As Microsoft.Office.Interop.Excel.Range) _
      Handles NamedRange1.Change
  
      With Me.NamedRange2
          .Value2 = "The Change event occurred."
          .Font.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Blue)
          .Font.Italic = False 
      End With 
  End Sub
  

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

  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.

Adding Code to Respond to the SelectionChange Event

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
  

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

  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.

Testing the Application

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.

Next Steps

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

• Deploying the project. For more information, see Deploying an Office Solution.

See Also

Tasks

How to: Resize NamedRange Controls

How to: Add NamedRange Controls to Worksheets

How to: Create Event Handlers in Office Projects

Concepts

Host Items and Host Controls Overview

Automating Excel by Using Extended Objects

NamedRange Control

Programmatic Limitations of Host Items and Host Controls