How to: Add ListObject controls to worksheets

  • Article

Applies to: yesVisual Studio noVisual Studio for Mac

Note

This article applies to Visual Studio 2017. If you're looking for the latest Visual Studio documentation, see Visual Studio documentation. We recommend upgrading to the latest version of Visual Studio. Download it here

You can add ListObject 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 VSTO Add-in projects for Excel. For more information, see Features available by Office application and project type.

You can also add ListObject controls at run time in VSTO Add-in projects.

This topic describes the following tasks:

Add 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 Personalize the IDE.

To use the Ribbon in Excel

  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 Add new connections.

  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.

Add 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 Add controls to Office documents at run time.

To add a ListObject control to a worksheet programmatically

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

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

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

Add ListObject controls at run time in a VSTO Add-in project

You can add a ListObject control programmatically to any open worksheet in a VSTO Add-in 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 Extend Word documents and Excel workbooks in VSTO Add-ins at run time.

To add a ListObject control to a worksheet programmatically

  1. 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 void AddListObject()
{
    Worksheet worksheet = Globals.Factory.GetVstoObject(
        Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets[1]);

    Microsoft.Office.Tools.Excel.ListObject list1;
    Excel.Range cell = worksheet.Range["$A$1:$D$4"];
    list1 = worksheet.Controls.AddListObject(cell, "list1");
}

    Private Sub AddListObject()
    Dim NativeWorksheet As Microsoft.Office.Interop.Excel.Worksheet =
        Globals.ThisAddIn.Application.ActiveWorkbook.Worksheets(1)

    Dim worksheet As Microsoft.Office.Tools.Excel.Worksheet =
        Globals.Factory.GetVstoObject(NativeWorksheet)

    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

See also