How to Use VBA to Toggle Excel Table Header and Banded Rows
Question details
The user wants to use VBA macros to enable or disable the Header Row and Banded Rows options in Excel tables, either by specifying a table name or by operating on the current selection.

- Product
- Excel
- Device & OS
- not provided
- Scenario
- Automating the formatting of Excel tables to dynamically hide or show headers and banded row styling using VBA scripts.
- Observed behavior
- The user needs the precise VBA syntax and methods, such as ListObject properties or ExecuteMso commands, to programmatically control table style options.
Ensure the Developer tab is enabled in your spreadsheet program and that your workbook is saved as a Macro-Enabled Workbook (.xlsm) before writing or running VBA code.
Turn Off Table Options Using ListObject Properties
The most direct way to toggle table headers and banded rows is by referencing the specific table name using ListObject properties.
By utilizing the ListObjects collection of the ActiveSheet, you can target a specific table by its name and explicitly set its header and stripe visibility to True or False.
Press Alt + F11 on your keyboard to launch the VBA Editor, then click Insert > Module to create a new script area.
Inside your new Sub procedure, type `ActiveSheet.ListObjects("Table1").ShowHeaders = False` to hide the table's header row.
On the next line, type `ActiveSheet.ListObjects("Table1").ShowTableStyleRowStripes = False` to remove the banded row styling.
Replace 'Table1' in the code with the actual name of your table. Press F5 to run the macro and apply the changes to your worksheet.

Toggle Options for the Currently Selected Table
Use a VBA loop to check for tables intersecting with your active selection, allowing you to toggle options without knowing the exact table name.
Clear Options Using ExecuteMso
Simulate clicking the ribbon buttons for Header Row and Banded Rows using the CommandBars.ExecuteMso method.
Easily Manage Tables and Macros in WPS Spreadsheet
WPS Spreadsheet provides excellent compatibility with Microsoft Excel, allowing you to run your existing VBA macros to manage table headers, banded rows, and other formatting properties seamlessly.
- 1. Install WPS Office: Download and install the free WPS Office suite on your computer.
- 2. Open your workbook: Launch WPS Spreadsheet and open your macro-enabled workbook (.xlsm).
- 3. Enable macros: Click 'Enable Macros' if prompted by the security warning at the top of the screen.
- 4. Run your scripts: Press Alt + F11 to open the WPS Macro Editor, and execute your table formatting scripts exactly as you would in Excel.

Frequently Asked Questions
How can I turn the table header back on using VBA?
To re-enable the table header, simply change the Boolean value in your code to True: `ActiveSheet.ListObjects("Table1").ShowHeaders = True`.
Why isn't my VBA code affecting the banded rows?
Ensure your table actually has a Table Style applied that includes banded row formatting. If the applied style is completely plain, toggling `ShowTableStyleRowStripes` will not visually change the table.
Can I toggle banded columns and banded rows at the same time?
Yes, you can manage banded columns by adding `ActiveSheet.ListObjects("Table1").ShowTableStyleColumnStripes = True` (or False) to your existing VBA script.
How do I find out the exact name of my Excel table?
Click any cell inside your table, then look at the Name box located to the left of the formula bar. Alternatively, go to the Table Design tab on the ribbon where the table name is listed on the far left.




