How to: Add NamedRange Controls to Worksheets

You can add NamedRange controls to a Microsoft Office Excel worksheet at design time and at run time in document-level projects.

Applies to: The information in this topic applies to document-level projects and application-level projects for Excel 2007 and Excel 2010. For more information, see Features Available by Office Application and Project Type.

You can also add NamedRange controls at run time in application-level add-in projects.

This topic describes the following tasks:

• Adding NamedRange controls at design time

• Adding NamedRange controls at run time in a document-level project

• Adding NamedRange controls at run time in an application-level project

For more information about NamedRange controls, see NamedRange Control.

Adding NamedRange Controls at Design Time

There are several ways to add NamedRange controls to a worksheet in a document-level project at design time: from within Excel, from the Visual Studio Toolbox, and from the Data Sources window.

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

To add a NamedRange control to a worksheet using the Name Box in Excel

1. Select the cell or cells you want to include in the named range.

2. In the Name Box, type a name for the range and press ENTER.

   The Name Box is located beside the formula bar, just above column A of the worksheet.

To add a NamedRange control to a worksheet using the Toolbox

1. Open the Toolbox and click the Excel Controls tab.

2. Click NamedRange and drag it to a worksheet.

   The Add Named Range dialog box appears.

3. Select the cell or cells you want to include in the named range.

4. Click OK.

   If you do not want the default name that is given to the control, you can change the name in the Properties window.

To add a NamedRange control to a worksheet using the Data Sources window

1. Open the Data Sources window and create a data source for your project. For more information, see How to: Connect to Data in a Database.

2. Drag a single field from the Data Sources window to your worksheet.

   A data-bound NamedRange control is added to the worksheet. For more information, see Data Binding and Windows Forms.

Adding NamedRange Controls at Run Time in a Document-Level Project

You can add a NamedRange control programmatically to your worksheet at run time. This enables you to create the host controls in response to events. Dynamically created named ranges are not persisted in the worksheet as host controls when the worksheet is closed. For more information, see Adding Controls to Office Documents at Run Time.

To add a NamedRange control to a worksheet programmatically

• In the Startup event handler of Sheet1, insert the following code to add the NamedRange control to cell A1 and set its Value2 property to Hello world!

  Dim textInCell As Microsoft.Office.Tools.Excel.NamedRange
  textInCell = Me.Controls.AddNamedRange(Me.Range("A1"), "cellText")
  
  textInCell.Value2 = "Hello world!"
  

  Microsoft.Office.Tools.Excel.NamedRange textInCell;
  textInCell = this.Controls.AddNamedRange(this.get_Range("A1", missing), "cellText");
  
  textInCell.Value2 = "Hello world!";
  

Adding NamedRange Controls at Run Time in an Application-Level Project

You can add a NamedRange control programmatically to any open worksheet in an application-level project. Dynamically created named ranges are not persisted in the worksheet as host controls when the worksheet is closed. For more information, see Extending Word Documents and Excel Workbooks in Application-Level Add-ins at Run Time.

To add a NamedRange control to a worksheet programmatically

• The following code generates a worksheet host item that is based on the open worksheet, and then adds a NamedRange control to cell A1 and sets its Value2 property to Hello world.

  Private Sub AddNamedRange()
      Dim textInCell As Microsoft.Office.Tools.Excel.NamedRange
  
      Dim NativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet =
          Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(1)
  
      ' Use the following line of code in projects that target the .NET Framework 4.
      Dim worksheet As Microsoft.Office.Tools.Excel.Worksheet =
          Globals.Factory.GetVstoObject(NativeWorksheet)
  
      ' In projects that target the .NET Framework 3.5, use the following line of code.
      ' Dim worksheet = CType(Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(1),  _
      '    Excel.Worksheet).GetVstoObject()
  
      Dim cell As Excel.Range = worksheet.Range("A1")
      textInCell = worksheet.Controls.AddNamedRange(cell, "MyNamedRange")
      textInCell.Value2 = "Hello World"
  End Sub
  

  private void AddNamedRange()
  {
      Microsoft.Office.Tools.Excel.NamedRange textInCell;
  
      // Use the following line of code in projects that target the .NET Framework 4.
      Worksheet worksheet = Globals.Factory.GetVstoObject(
          Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]);
  
      // In projects that target the .NET Framework 3.5, use the following line of code.
      // Worksheet worksheet = 
      //     ((Excel.Worksheet)Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]).GetVstoObject();
  
      Excel.Range cell = worksheet.Range["A1", missing]; 
      textInCell = worksheet.Controls.AddNamedRange(cell, "MyNamedRange");
      textInCell.Value2 = "Hello World";
   }
  

See Also

Tasks

How to: Resize NamedRange Controls

Concepts

Extending Word Documents and Excel Workbooks in Application-Level Add-ins at Run Time

NamedRange Control

Automating Excel by Using Extended Objects

Host Items and Host Controls Overview

Programmatic Limitations of Host Items and Host Controls

Other Resources

Controls on Office Documents