Fix Excel VBA RefreshAll Errors on Protected Sheets in WPS Spreadsheets
Question details
The user needs to stop a VBA macro from failing when it unprotects worksheets, runs ActiveWorkbook.RefreshAll, and protects the worksheets again while data connections are still refreshing in the background.
- Product
- WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- A macro refreshes all workbook data connections and then reapplies worksheet protection.
- Observed behavior
- Worksheets are protected again before background data connections finish, causing the Excel VBA RefreshAll macro to fail.
Open the macro-enabled workbook in WPS Spreadsheets, press Alt+F11 to open the VBA editor, and locate the Workbook_RefreshAll macro or the module that calls RefreshAll. Note which data connections are set to refresh in the background before changing any code.
Unprotect Sheets and Disable Background Refresh Before Running RefreshAll
Use this when the macro reprotects worksheets immediately after RefreshAll; it makes data connections finish synchronously so no refresh is still running.
RefreshAll applies to the entire workbook, not to a single worksheet. Protecting sheets too early interrupts a connection that is still updating in the background.
In WPS Spreadsheets, click the Developer tab and choose Visual Basic, or press Alt+F11 to open the VBA editor.
In the macro, add or keep a loop that unprotects every required worksheet before calling ActiveWorkbook.RefreshAll, for example: For Each ws In ActiveWorkbook.Worksheets: ws.Unprotect Password:="yourpassword": Next ws.
Before ActiveWorkbook.RefreshAll, set BackgroundQuery to False for each connection or query table so RefreshAll does not return while data is still loading.
Call ActiveWorkbook.RefreshAll after all sheets are unprotected and connection background refresh is disabled; remove any worksheet-level RefreshAll loop.
Add the worksheet Protect code after RefreshAll, and confirm each connection is no longer refreshing before the protection lines run.
Wait for Background Connections to Finish Before Protecting Sheets
Use this if you must keep background refresh enabled for some data connections.
Fix Excel VBA RefreshAll Errors on Protected Sheets in WPS Office
Open the macro-enabled workbook in WPS Office Spreadsheets and use the built-in VBA editor to unprotect worksheets, set data connections to synchronous refresh, and run RefreshAll exactly once. WPS Office keeps the Excel VBA workflow familiar while protecting your sheets after the data refresh completes.
- 1. Download WPS Office: Go to the official WPS website and download WPS Office Free, then install it on your computer.
- 2. Open the Excel macro workbook: Launch WPS Spreadsheets and open the .xlsm or .xlsx workbook that contains the failing RefreshAll macro.
- 3. Open the VBA editor: Click the Developer tab and select Visual Basic, or press Alt+F11, to open the VBA editor.
- 4. Edit the macro: Unprotect all required worksheets, disable background refresh for the data connections, and call ActiveWorkbook.RefreshAll once before protecting sheets again.
- 5. Save and run the macro: Save the macro-enabled workbook, run the macro, and confirm that the sheets are protected only after all data connections have finished refreshing.
Frequently Asked Questions
Does ActiveWorkbook.RefreshAll refresh only the active worksheet?
No. RefreshAll refreshes all data connections in the entire workbook. A worksheet loop is usually unnecessary when the goal is to refresh the whole workbook.
Why does the macro fail after RefreshAll before protecting sheets?
Data connections may continue refreshing in the background after RefreshAll returns. If sheets are protected while a background query is still writing data, Excel or WPS Spreadsheets can raise an error.
How do I disable background refresh for a connection in VBA?
Open the VBA editor with Alt+F11, locate the connection or query table, and set its BackgroundQuery property to False before calling ActiveWorkbook.RefreshAll.
Can I fix this Excel VBA macro in WPS Office?
Yes. Open the macro-enabled workbook in WPS Spreadsheets, use the Developer tab or Alt+F11 to open the VBA editor, and apply the same RefreshAll ordering and background refresh settings.
What if I cannot disable background refresh?
Keep the worksheets unprotected after RefreshAll and add a wait condition that checks each connection or query table's Refreshing property until it returns False, then protect the sheets.
