Working with Table List Object Properties in Excel 2010
Working with Tables in Microsoft Office 2010: Learn how to work with the ListObject object to change the styles of a table in Microsoft Excel 2010.
Last modified: May 20, 2011
Applies to: Excel 2010 | Office 2010 | PowerPoint 2010 | VBA | Word 2010
Published: June 2011
Provided by: Frank Rice, Microsoft Corporation
The ListObject object displays data in a series of rows and columns. In this topic, you programmatically work with various members of the ListObject object to change the display styles of a table in Microsoft Excel 2010. To complete this task, you must do the following:
In this task, you add programming code to the Visual Basic Editor.
To add code to the Visual Basic Editor
Start Excel 2010.
On the Developer tab, click Visual Basic to open the Visual Basic Editor.
If you do not see the Developer tab in Excel 2010, click the File tab, and then click Options. In the categories pane, click Custom Ribbon, select Developer, and then click OK.
In the Projects pane, click Sheet1.
Paste or type the following Microsoft Visual Basic for Applications (VBA) code into the module window.
Sub ListObjectDisplayDemo() ' Step over this procedure. It's not terribly interesting. FillRandomData Dim lo As ListObject Set lo = ListObjects.Add( _ SourceType:=xlSrcRange, _ Source:=Range("A1:F13"), _ XlListObjectHasHeaders:=xlYes) lo.Name = "SampleData" ' See the ListTableStyles procedure to create a list of named styles: lo.TableStyle = "TableStyleLight2" ' Single step through the following code to see the behavior: lo.ShowHeaders = False lo.ShowHeaders = True lo.ShowTableStyleColumnStripes = True lo.ShowTableStyleColumnStripes = False lo.ShowTableStyleFirstColumn = True lo.ShowTableStyleFirstColumn = False lo.ShowTableStyleLastColumn = True lo.ShowTableStyleLastColumn = False lo.Comment = "This is a sample table. Check the Formulas|Name Manager to see the comment." ' Delete the List object so you can run the code again if you like. lo.Delete End Sub Sub FillRandomData() ' No need to stop through this procedure. Range("A1", "F1").Value = Array("Month", "North", "South", "East", "West", "Total") ' Fill in twelve rows with random data. Dim i As Integer Dim j As Integer For i = 1 To 12 Cells(i + 1, 1).Value = MonthName(i, True) For j = 2 To 5 Cells(i + 1, j) = Round(Rnd * 100) Next j Next i Range("F2", "F13").Formula = "=SUM(B2:E2)" End Sub Sub ListTableStyles() ' Use this procedure to list all the named table styles in the ' Immediate window. Put the cursor in this procedure and press F5 ' to run it. Look in the Immediate window for the results. Dim ts As TableStyle For Each ts In ActiveWorkbook.TableStyles Debug.Print ts.Name Next ts End Sub
In this task, you add a table to a worksheet and then step through the VBA code to see the effects on the display styles of the table.
To step through the code
Drag the Visual Basic Editor window to the right side of your monitor.
Drag the Excel window to the left side of your monitor and adjust the windows until you can see them both.
Now, place the cursor in ListObjectDisplayDemo procedure, press F8 to get started debugging, and then press Shift+F8 to step through the code line-by-line (and bypass external procedures) and watch the code behavior.