How to make Power Query refresh on a protected sheet in Excel

Power Query in Excel does not allow refreshing queries that load to tables in protected sheets.

To refresh such a query, we need to unprotect the sheet first and then protect it again after the query refreshing process is complete.

A simple macro will do the trick, but there is a very simple detail that we must not forget, or the macro will not work.

The Problem:

We have a file with a query loading to a table in a sheet that we then protect. In that sheet, we have a button to run a VBA macro to unprotect the active sheet, refresh our query and then reapply the sheet protection. However, we get this error when we try to refresh the query.

“The cell or chart you’re trying to change is on a protected sheet. To make a change, unprotect the sheet. You might be required to enter a password.”

How to make Power Query refresh on a protected sheet in Excel - Celia Alves

The Solution:

There is a very simple solution to this annoying problem. Go to the Queries and Connections pane. Right-mouse-click on the query name and choose “properties” from the drop-down menu.

Uncheck the box for “Enable background refresh” and click OK.

Another way to access this property is by selecting the query in the Queries and Connections pane and then going to the Data tab and clicking Properties. Then, click the icon to the right of the query name to access the enable background refresh property checkbox.

When Enable background refresh has the tick mark, we are telling the system to carry on with any other stuff (actions) it needs to do while the query refreshes in the background.

But we do not want that. We want Excel to stop everything else until Power Query finishes refreshing the query and our data is updated. Then, the macro can carry on with the next move that we programmed.

Once we change the enable background property, the refresh completes while the sheet is unprotected, and only after that VBA protects the sheet, and the error will not show up. (If the error still shows up for you, please read until the end.)

Watch the full lesson to learn how to build the macro and adjust all the details. You can watch me changing the enable background refresh property at minute 28:20 in this video:

The VBA code that I used in the demo file is the following:

Copy to Clipboard

Although this solution works in some cases, I have also seen situations where unchecking the “Enable background property” is not enough to make VBA wait for the query to finish refreshing. If that’s the case for you, what has worked for me to enforce VBA to wait for Power Query is to add the query results to the data model, even if you do not need a data model in your file.