strange protected sheet issue, refresh query tables

I'm having a strange problem when I attempt to refresh some query tables on a protected sheet.

Prior to the refresh, I have included a line to unprotect the sheet (see below) and after the refresh, I protect it again.

When I step through this code, one line at a time using F8, it works perfectly. But when I run the code in the normal way, I get a pop-up telling me "The cell I'm trying to change is on a protected sheet."

Anyone encountered something like this before? Again, it works when I step through the code - but only then.

Please Login or Register  to view this content.

Try protecting the sheet only for the user interface, so that instructions run via VBA act as if the sheet is unprotected:

Please Login or Register  to view this content.

With the sheet protected this way, you don't need the 'unprotect' command at the start of your routine. Note this protection needs applied when the workbook is opened, so I'd suggest triggering this protection line in the workbook_open event.

Please Login or Register  to view this content.

Tried, that still doesn't seem to work.

Try enabling background refresh on your data queries.

That was already done.


It's really strange, but I've checked it and it definitely is running the refresh as desired IF i step-through it. As soon as I run it normally, it get a popup for both refresh statements - and the refresh doesn't happen either.

It sounds like the sheet protection is happening before the data refresh is finished - but protecting the sheet userinterface only should prevent that from being a problem...

I'm out of ideas, I'm afraid.

I think you must disable background refresh.

AH HA!


I tried setting to false - but did it in the wrong place. You DON'T disable it in the user interface - you do it in the code. I dressed the macro up a bit more while I was at it, but the key was passing false to the "BackgroundQuery" parameter. Edit: I think unchecking the box should have worked in the user interface also - but only if I left the parameter out of the code. Doing it in the code is a little more fool proof.

Setting FALSE kept the worksheet unlocked until the refresh was complete.

Your comment and the default F1 help saved the day :D

Description of the BackgroundRefresh Parameter: Used only with QueryTables that are based on the results of a SQL query. True to return control to the procedure as soon as a database connection is made and the query is submitted. The QueryTable is updated in the background. False to return control to the procedure only after all data has been fetched to the worksheet. If this argument isn't specified, the setting of the BackgroundQuery property determines the query mode.

Please Login or Register  to view this content.

Glad you figured it out