Class ListObject (Excel VBA)

The class ListObject represents a list object in the ListObjects collection.

The main procedures of class ListObject are Delete and ListObjects.Add

Set

To use a ListObject class variable it first needs to be instantiated, for example

Dim ws As Worksheet: Set ws = 
Dim lstobj As ListObject
Set lstobj = ws.ListObjects.Add(Source:="", TableStyleName:="")

The following procedures can be used to set variables of type ListObject: ListObjects.Item, ListObjects.Add, QueryTable.ListObject, Range.ListObject, SlicerCache.ListObject, TableObject.ListObject and Worksheet.ListObjects

For Each

Here is an example of processing the ListObject items in a collection.

Dim ws As Worksheet: Set ws = 
Dim lstobjListObject As ListObject
For Each lstobjListObject In ws.ListObjects
	
Next lstobjListObject

Themes

Some procedures in this class have been grouped together in themes and are described on separate theme pages

Display/Show with its procedures DisplayName, DisplayRightToLeft, ShowHeaders and ShowTotals
Row with its procedures HeaderRowRange, InsertRowRange, ListRows and TotalsRowRange

Methods

These are the main methods of the ListObject class

Delete - Deletes the ListObject object and clears the cell data from the worksheet.

ListObjects.Add - Creates a new list object.

Set objListObject = ActiveWorkbook.Worksheets(1).ListObjects.Add(SourceType:= xlSrcExternal, _ 
Source:= Array(strServerName, strListName, strListGUID), LinkSource:=True, _ 
XlListObjectHasHeaders:=xlGuess, Destination:=Range("A1")), 
TableStyleName:=xlGuess, Destination:=Range("A10")) 

Other Methods

Publish - Publishes the ListObject object to a server that is running Microsoft SharePoint Foundation.

Properties

Active returns a Boolean value indicating whether a ListObject object on a worksheet is active—that is, whether the active cell is inside the range of the ListObject object.

Function MakeListActive() As Boolean 
 Dim wrksht As Worksheet 
 Dim objList As ListObject 
 
 Set wrksht = ActiveWorkbook.Worksheets("Sheet1") 
 Set objList = wrksht.ListObjects(1) 
 objList.Range.Activate 
 
 MakeListActive = objList.Active 
End Function

AlternativeText returns or sets the descriptive (alternative) text string for the specified table.

AutoFilter filters a table using the AutoFilter feature.

Comment returns or sets the comment associated with the list object.

DataBodyRange returns a Range object that represents the range of values, excluding the header row, in a table.

Worksheets("Sheet1").Activate 
ActiveSheet.ListObjects.Item(1).DataBodyRange.Select

ListColumns returns a ListColumns collection that represents all the columns in a ListObject object.

Sub DisplayColumnName 
 Dim wrksht As Worksheet 
 Dim objListObj As ListObject 
 Dim objListCols As ListColumns 
 
 Set wrksht = ActiveWorkbook.Worksheets("Sheet1") 
 Set objListObj = wrksht.ListObjects(1) 
 Set objListCols = objListObj.ListColumns 
 
 Debug.Print objListCols(2).Name 
End Sub

Name returns or sets a String value that represents the name of the ListObject object.

Sub Test 
 Dim wrksht As Worksheet 
 Dim oListObj As ListObject 
 
 Set wrksht = ActiveWorkbook.Worksheets("Sheet1") 
 Set oListObj = wrksht.ListObjects(1) 
 
 MsgBox oListObj.Name 
End Sub

Parent returns the parent object for the specified object. Read-only.

QueryTable returns the QueryTable object that provides a link for the ListObject object to the list server.

Dim wrksht As Worksheet 
 Dim objListObj As ListObject 
 Dim objQryTbl As QueryTable 
 Dim prpQryProp As pro 
 Dim arTarget(4) As String 
 Dim strSTSConnection As String 
 
 Set wrksht = ActiveWorkbook.Worksheets("Sheet1") 
 Set objListObj = wrksht.ListObjects(1) 
 
 arTarget(0) = "0" 
 arTarget(1) = "https://myteam/project1" 
 arTarget(2) = "1" 
 arTarget(3) = "List1" 
 
 strSTSConnection = objListObj.Publish(arTarget, True) 
 
 Set objQryTbl = objListObj.QueryTable 
 
 objQryTbl.MaintainConnection = True

Range returns a Range object that represents the range to which the specified list object in the list applies.

SharePointURL returns a String representing the URL of the SharePoint list for a given ListObject object.

Sub PublishList() 
 Dim wrksht As Worksheet 
 Dim objListObj As ListObject 
 Dim arTarget(4) As String 
 Dim strSTSConnection As String 
 
 Set wrksht = ActiveWorkbook.Worksheets("Sheet1") 
 Set objListObj = wrksht.ListObjects(1) 
 
 arTarget(0) = "0" 
 arTarget(1) = objListObj.SharePointURL 
 arTarget(2) = "1" 
 arTarget(3) = objListObj.Name 
 
 strSTSConnection = objListObj.Publish(arTarget, True) 
End Sub

ShowAutoFilter returns Boolean to indicate whether the AutoFilter will be displayed.

 
 Dim wrksht As Worksheet 
 Dim oListCol As ListColumn 
 
 Set wrksht = ActiveWorkbook.Worksheets("Sheet1") 
 Set oListCol = wrksht.ListObjects(1) 
 
 Debug.Print oListCol.ShowAutoFilter

ShowAutoFilterDropDown true when the AutoFilter drop-down for the ListObject object is displayed.

ShowTableStyleColumnStripes returns or sets if the Column Stripes table style is used for the specified ListObject object.

ShowTableStyleFirstColumn returns or sets if the first column is formatted for the specified ListObject object.

ShowTableStyleLastColumn returns or sets if the last column is displayed for the specified ListObject object.

ShowTableStyleRowStripes returns or sets if the Row Stripes table style is used for the specified ListObject object.

Slicers returns a list of the table slicers associated with a ListObject.

Sort gets or sets the sort column or columns and sort order for the ListObject collection.

SourceType returns an XlListObjectSourceType value that represents the current source of the list.

Sub Test () 
 Dim wrksht As Worksheet 
 Dim oListObj As ListObject 
 
 Set wrksht = ActiveWorkbook.Worksheets("Sheet1") 
 Set oListObj = wrksht.ListObjects(1) 
 
 Debug.Print oListObj.SourceType 
End Sub

Summary returns or sets the description associated with the alternative text string for the specified table.

TableObject returns a TableObject object.

TableStyle gets or sets the table style for the specified Listtablestyle tablestyle. Read/write Variant.

XmlMap returns an XmlMap object that represents the schema map used for the specified table.

ListObjects.Count returns an Integer value that represents the number of objects in the collection.

ListObjects.Item returns a single object from a collection.

 
 Set wrksht = ActiveWorkbook.Worksheets("Sheet1") 
 Set oListObj = wrksht.ListObjects.Item(1).Name

ListObjects.Parent returns the parent object for the specified object. Read-only.