How to: Add ListObject Controls to Worksheets

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 add ListObject controls to a Microsoft Office Excel worksheet at design time and at run time in document-level projects.

Starting in Visual Studio 2008 Service Pack 1 (SP1), you can add ListObject controls at run time in application-level add-in projects.

This topic describes the following tasks:

• Adding ListObject controls at design time

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

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

For more information about ListObject controls, see ListObject Control.

Adding ListObject Controls at Design Time

There are several ways to add ListObject 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 use the Create List dialog box in Excel 2003

1. On the Data menu, point to Microsoft Office Excel Data, point to List, and then click Create List.

   The Create List dialog box appears.

   Note

   If Microsoft Office Excel Data is not available on the Data menu, click the worksheet to give it focus.

2. Select the cell or cells you want to include in the list and click OK.

To use the Ribbon in Excel 2007

1. On the Insert tab, in the Tables group, click Table.

2. Select the cell or cells you want to include in the list and click OK.

To use the Toolbox

1. From the Excel Controls tab of the Toolbox, drag a ListObject to the worksheet.

   The Add ListObject Control dialog box appears.

2. Select the cell or cells you want to include in the list and click OK.

   If you do not want to keep the default name, you can change the name in the Properties window.

To use 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 table from the Data Sources window to your worksheet.

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

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

You can add the ListObject control dynamically at run time. This enables you to create the host controls in response to events. Dynamically created list objects 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 ListObject control to a worksheet programmatically

• In the Startup event handler of Sheet1, insert the following code to add a ListObject control to cells A1 through A4.

  Dim employeeData As Microsoft.Office.Tools.Excel.ListObject
  employeeData = Me.Controls.AddListObject(Me.Range("$A$1:$D$4"), "employees")
  

  Microsoft.Office.Tools.Excel.ListObject employeeData;
  employeeData = this.Controls.AddListObject(this.get_Range("$A$1:$D$4", missing), "employees");
  

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

Starting in SP1, you can add a ListObject control programmatically to any open worksheet in an application-level project. Dynamically created list objects are not persisted in the worksheet as host controls when the worksheet is saved and then closed. For more information, see Extending Word Documents and Excel Workbooks in Application-Level Add-ins at Run Time.

To add a ListObject control to a worksheet programmatically

• The following code generates a worksheet host item that is based on the open worksheet, and then adds a ListObject control to cells A1 through A4.

  Private Sub AddListObject()
      Dim worksheet As Worksheet = CType(Application.ActiveWorkbook.Worksheets(1),  _
          Excel.Worksheet).GetVstoObject()
      Dim list1 As Microsoft.Office.Tools.Excel.ListObject
      Dim cell As Excel.Range = worksheet.Range("$A$1:$D$4")
      list1 = worksheet.Controls.AddListObject(cell, "MyListObject")
  End Sub
  

  private void AddListObject()
  {
      Worksheet worksheet =
          ((Excel.Worksheet)Application.
                ActiveWorkbook.Worksheets[1]).GetVstoObject();
      Microsoft.Office.Tools.Excel.ListObject list1;
      Excel.Range cell = worksheet.Range["$A$1:$D$4", missing];
      list1 = worksheet.Controls.AddListObject(cell, "list1");
  }
  

See Also

Tasks

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

ListObject Control

Host Items and Host Controls Overview

Binding Data to Controls in Office Solutions

Programmatic Limitations of Host Items and Host Controls

Other Resources

Controls on Office Documents

Excel Host Controls

Change History

Date	History	Reason
July 2008	Added a section that shows how to add a list object control to a worksheet in an application-level add-in.	SP1 feature change.