Data table refresh called from VBA not finishing before moving on to next line

[tbaker818]

I've run into this problem many times over the last few years, never with any kind of resolution. VBA seems to have a consistent problem where it won't finish a data table refresh before continuing. Normally, you probably wouldn't notice this is happening, but if you're closing a workbook or re-enabling protection later in the code, you'll have problems. If you're closing, it will warn you that closing the workbook will cancel a pending refresh.

The latest issue I have is with re-enabling protection after calling for a data table refresh. The refresh doesn't complete before the protection is re-enabled and which triggers a protection error and prevents the refresh from happening. There's a UserInterfaceOnly argument in the VBA Protect method which should get around the problem of having to unprotect and then re-protect entirely. I've confirmed that this works with basic operations like making an entry in a cell, but it does not work when refreshing Data Tables (major oversight?). So it seems like this workaround has failed as well which is infuriating. I've run into this problem (VBA not completing refresh) many times over the last few years, never with any kind of resolution and It seems that once again I have no workaround here. Has anyone else come up with anything for these situations?

[romperstomper]

If you are refreshing a specific query in your code (rather than using RefreshAll) you can specify the BackGroundQuery:=False parameter. You can also manually disable background refreshing in the properties for the query/table.

[tbaker818]

Thanks, that worked!

[romperstomper]

Glad to help.

Please don't forget to mark the thread solved using the Thread tools link at the top of the page.