Here’s a utility I’ve used for years. It lets you manage True/False properties for structured tables with data connections – for example BackgroundQuery and PreserveColumnInfo. You can change properties for all tables in a workbook at once, or just one or a few tables.

I only added some properties to the form. One of these, MaintainConnection, can’t be found in Excel’s front end, and the others appear in two different dialog boxes. So being able to change them for multiple tables in one dialog is really nice. For example, above I’d select all three tables using Ctrl-A and uncheck AdjustColumnWidth and MaintainConnection. You can see that currently both highlighted tables have AdjustColumnWidth turned off. Also, a gray checkmark, like the one for MaintainConnection, means that some selected tables have it turned on but others don’t.

Features

• Select which tables to modify. Ctrl-A selects all tables in the workbook. The Choose Current Table button selects the table where the active cell is located.
• The current settings for the selected tables are shown by the state of the checkboxes. The checkboxes are triple-state, so if the property is true for only some of of the selected tables, the checkmark will be gray, as with MaintainConnection in picture.
• To change properties for selected tables, set them with the checkboxes, then click Apply.
• Double-clicking a table activates the sheet it’s on.
• To add another property to the VBA form, simply add a checkbox inside the grpProperties frame of the form. The checkbox caption must be the exact name of a Boolean property.

The VBA

I hadn’t looked at the VBA for this for a while, except to add MaintainConnection a year or so ago. The code was clunky in that it referred to each property individually in a couple of places, something like:

… and so on for each property/checkbox assignment.

I realized that CallByName would work great here. If you don’t know it, CallByName is kind of like an INDIRECT function for object properties and methods. So instead of the list above, I used something like this inside of a loop:

• lo.QueryTable is the QueryTable object
• ctl.Caption contains the property name, e.g., MaintainConnection
• chk.Value is the value of the checkbox, i.e. True or False

To see the actual code and the working form go to the download link below.

A note about MaintainConnection

I’ve mentioned MaintainConnection a couple of times now. It’s the key to an issue that comes up once a year or so, and adding this property to the form helps me remember the solution.

The issue occurs when connecting a structured table to another workbook. If MaintainConnection is True and I refresh the table, it locks the source workbook and it can’t be edited. I tried changing the connection to read only and fiddling with other connection settings, but setting MaintainConnection to false solves the issue. And this form makes it easy to do for every table in the workbook.

Download

Here’s a sample workbook with the form and some tables to test it on. Let me know what you think!