This documentation is archived and is not being maintained.

ListObject Control

Note Required applications

The features in this topic are available only if you have the required applications installed.

For more information, see Features Available by Product Combination.

  • One of these development environments:

    VSTO 2005

    -or-

    Visual Studio Team System

  • Microsoft Office 2003

The ListObject control is a list that exposes events and can be bound to data. When you add a list to a worksheet, Microsoft Visual Studio 2005 Tools for the Microsoft Office System creates a ListObject control that you can program against directly without having to traverse the Microsoft Office Excel 2003 object model.

Creating the Control

You can create a ListObject control from within Excel or Visual Studio. For more information, see How to: Add ListObject Controls to Worksheets.

Binding Data to the Control

A ListObject control supports simple and complex data binding. The ListObject control can be bound to a data source using the DataSource and DataMember properties at design time or the SetDataBinding method at run time.

NoteNote

The ListObject is updated automatically when it is bound to a data source, such as a DataTable, that raises events when the data changes. If you bind the ListObject to a data source that does not raise events when the data changes, you must call the RefreshDataRow or RefreshDataRows method to update the ListObject.

When you add a ListObject to a worksheet cell by mapping a repeating schema element to that cell, Visual Studio Tools for Office automatically maps the ListObject to the generated dataset. However, the ListObject is not automatically bound to the data. You can manually bind the ListObject to the dataset at design time or programmatically bind the ListObject to the dataset at run time. Because the data is separate from the ListObject, you should add and remove data through the bound dataset, and not directly through the ListObject. If the data in the bound dataset is updated through any mechanism, the ListObject control automatically reflects the changes. For more information, see Data Model Overview.

You can quickly fill a ListObject control by binding the ListObject to a data source. If you edit the data in a data-bound ListObject, the changes are automatically made in the data source as well. If you want to fill a ListObject and then enable the user to change the data in the ListObject without modifying the data source, you can use the Disconnect method to detach the ListObject from the data source. For more information, see How to: Fill ListObject Controls with Data.

NoteNote

Data binding is not supported on overlapping ListObject controls.

Formatting

Formatting that can be applied to a Microsoft.Office.Interop.Excel.ListObject can be applied to a Microsoft.Office.Tools.Excel.ListObject control. This includes borders, fonts, number format, and styles. End-users can rearrange columns in a data-bound ListObject, and these changes will be persisted with the document, provided the ListObject was added to the document at design time. The next time the document is opened, the list object will be bound to the same data source, but the column order will reflect the users' changes.

Adding and Removing Columns at Run Time

You cannot manually add or remove columns in a data-bound ListObject control at run time. If an end-user tries to delete a column, it will immediately be restored and any columns added will be removed. Therefore, it is important to write code to explain to users why they cannot perform these actions on a ListObject that is bound to data. Visual Studio Tools for Office provides several events on a ListObject related to data binding. For example, you can use the OriginalDataRestored event to warn users that the data they have attempted to delete cannot be deleted and has been restored.

Adding and Removing Rows at Run Time

You can manually add and remove rows in a data-bound ListObject control, provided the data source allows the addition of new rows and is not read-only. You can write code against events such as the BeforeAddDataboundRow to validate the data, or the ErrorAddDataboundRow to retry once an error has been fixed.

Renaming ListObject Controls in Excel 2007

ListObject controls will continue to work if a user opens an Excel 2003 workbook or template solution in Excel 2007. However, although Excel 2007 enables users to change the name of Excel tables at run time by using the Design tab, the ListObject control does not support this feature. If a user tries to rename an Excel table that corresponds to a ListObject, the name of the Excel table will automatically revert to the original name when the workbook is saved.

NoteNote

Excel lists are called Excel tables in Excel 2007.

Events

See Also

Show: