How to: Resize NamedRange Controls

  • Article

Applies to

The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office.

Document-level projects

  • Excel 2007

  • Excel 2003

Application-level projects

  • Excel 2007

For more information, see Features Available by Application and Project Type.

You can set the size of a NamedRange control when you add it to a Microsoft Office Excel document; however, you might want to resize it at a later time.

You can resize a named range at design time or at run time in document-level projects.

Starting in Visual Studio 2008 Service Pack 1 (SP1), you can resize named ranges at run time in application-level add-ins.

This topic describes the following tasks:

  • Resizing NamedRange controls at design time

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

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

Resizing NamedRange Controls at Design Time

You can resize a named range by redefining its size in the Define Name dialog box.

To resize a named range by using the Define Name dialog box

  1. Right-click a NamedRange control.

  2. Click Manage Named Ranges on the shortcut menu.

    The Define Name dialog box appears.

  3. Select the named range you want to resize.

  4. Clear the Refers to box.

  5. Select the cells you want to use to define the size of the named range.

  6. Click OK.

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

You can resize a named range programmatically by using the RefersTo property.

Note

In the Properties window, the RefersTo property is marked as read-only.

To resize a named range programmatically

  1. Create a NamedRange control on cell A1 of Sheet1.

    Dim NamedRange1 As Microsoft.Office.Tools.Excel.NamedRange = _
    Me.Controls.AddNamedRange(Me.Range("A1"), "NamedRange1")

    Microsoft.Office.Tools.Excel.NamedRange NamedRange1 =
    this.Controls.AddNamedRange(this.Range["A1", missing], "NamedRange1");

  2. Resize the named range to include cell B1.

    NamedRange1.RefersTo = "=Sheet1!$A$1:$B$1"

    NamedRange1.RefersTo = "=Sheet1!$A$1:$B$1";

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

Starting in SP1, you can resize a NamedRange control on any open worksheet at run time. For more information about how to add a NamedRange control to a worksheet by using an application-level add-in, see How to: Add NamedRange Controls to Worksheets.

To resize a named range programmatically

  1. Create a NamedRange control on cell A1 of Sheet1.

    Dim worksheet As Worksheet = CType(Application.ActiveSheet,  _
 Excel.Worksheet).GetVstoObject()
Dim namedRange1 As Microsoft.Office.Tools.Excel.NamedRange
namedRange1 = worksheet.Controls.AddNamedRange(worksheet.Range("A1"), "MyNamedRange")

    Worksheet worksheet =
    ((Excel.Worksheet)Application.ActiveSheet).
        GetVstoObject();
Microsoft.Office.Tools.Excel.NamedRange namedRange1;        
namedRange1 = worksheet.Controls.AddNamedRange
    (worksheet.Range["A1", missing], "MyNamedRange");

  2. Resize the named range to include cell B1.

    namedRange1.RefersTo = "=Sheet1!$A$1:$B$1"

    namedRange1.RefersTo = "=Sheet1!$A$1:$B$1";

See Also

Tasks

How to: Add NamedRange Controls to Worksheets

How to: Delete NamedRange Controls at Design Time

How to: Resize Bookmark Controls

How to: Resize ListObject Controls

Concepts

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

Excel Application-Level Add-in Development

Adding Controls to Office Documents at Run Time

Host Items and Host Controls Overview

NamedRange Control

Other Resources

Controls on Office Documents

Excel Host Controls

Change History

Date

History

Reason

July 2008

Added a section that shows how to resize a named range control in an application-level add-in.

SP1 feature change.