Using Table Styles with the ListObject in Excel 2010

Office 2010

Office Quick Note banner

Working with Tables in Microsoft Office 2010: Learn how to work with the ListObject object to see the large number of styles available for a table in Microsoft Excel 2010.

Last modified: May 20, 2011

Applies to: Excel 2010 | Office 2010 | PowerPoint 2010 | Word 2010

In this article
Add the Code to the Visual Basic Editor
Test the Solution
Next Steps

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 display the vast number of styles available to you when working with 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

  1. Start Excel 2010.

  2. On the Developer tab, click Visual Basic to open the Visual Basic Editor.

    Note Note

    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.

  3. In the Projects pane, click Sheet1.

  4. Paste or type the following Microsoft Visual Basic for Applications (VBA) code into the module window.

    Sub ListObjectStyleDemo()
        ' Step over this procedure. It's not terribly interesting.
        Dim lo As ListObject
        Set lo = ListObjects.Add( _
         SourceType:=xlSrcRange, _
         Source:=Range("A1:F13"), _
        lo.Name = "SampleData"
        ' This code is only interesting if you single-step through it.
        Dim i As Integer
        For i = 1 To 20
            lo.TableStyle = ActiveWorkbook.TableStyles(i)
        Next i
        ' Use a named table style:
        lo.TableStyle = "TableStyleMedium9"
        Dim ts As TableStyle
        Set ts = lo.TableStyle
        With ts.TableStyleElements(xlHeaderRow)
            .Font.Bold = False
        End With
        CopyStyleElement ts.TableStyleElements(xlRowStripe1), Range("H2"), "xlRowStripe1"
        CopyStyleElement ts.TableStyleElements(xlHeaderRow), Range("H3"), "xlHeaderRow"
        ' With more complex table styles, could copy more styles to the output cells.
        ' Delete the table, so you can re-run the code.
    End Sub
    Sub CopyStyleElement(tse As TableStyleElement, rng As Range, title As String)
        On Error Resume Next
        With rng
            With .Interior
                .ThemeColor = tse.Interior.ThemeColor
                .TintAndShade = tse.Interior.TintAndShade
            End With
            With .Font
                .Color = tse.Font.Color
                .Name = tse.Font.Name
                .Size = tse.Font.Size
                .Bold = tse.Font.Bold
                .Italic = tse.Font.Italic
            End With
            .Value = title
        End With
    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 different display styles of the table.

To step through the code

  1. Drag the Visual Basic Editor window to the right side of your monitor.

  2. Drag the Excel window to the left side of your monitor and adjust the windows until you can see them both.

  3. Now, place the cursor in ListObjectStyleDemo 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.