VBA & Macro Problems

How to Use VBA to Toggle Excel Table Header and Banded Rows

Bushra ParveenBushra Parveen Nov 3, 2026 872 views

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.

How to Use VBA to Toggle Excel Table Header and Banded Rows
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.
Before you start

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.

Solution 1Recommended

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.

1
Open the VBA Editor

Press Alt + F11 on your keyboard to launch the VBA Editor, then click Insert > Module to create a new script area.

2
Write the code to disable the header

Inside your new Sub procedure, type `ActiveSheet.ListObjects("Table1").ShowHeaders = False` to hide the table's header row.

3
Write the code to disable banded rows

On the next line, type `ActiveSheet.ListObjects("Table1").ShowTableStyleRowStripes = False` to remove the banded row styling.

4
Customize and run the macro

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.

Turn Off Table Options Using ListObject Properties
Finding the Table Name: You can quickly check your table's name by clicking any cell inside it and looking at the Name box to the left of the formula bar, or by navigating to Formulas > Name Manager.
Manage Tables with WPS Spreadsheet

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. 1. Install WPS Office: Download and install the free WPS Office suite on your computer.
  2. 2. Open your workbook: Launch WPS Spreadsheet and open your macro-enabled workbook (.xlsm).
  3. 3. Enable macros: Click 'Enable Macros' if prompted by the security warning at the top of the screen.
  4. 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.
Fully compatible with Microsoft Excel (.xlsx and .xlsm) formatsSupports advanced VBA macros and ListObject propertiesLightweight design ensures fast loading and execution of complex scriptsFamiliar ribbon interface makes it easy to find Table Tools
microsoft office alternative - wps office

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.