Excel VBA not completing my code before next line

Question

Hello there i have the following VBA in an excel sheet.

Private Sub Workbook_Open()

    Application.EnableEvents = True
    
    Worksheets("BudgetInput").Unprotect Password:="password"
    Worksheets("BudgetInput").Range("G3:G150").Locked = True
    
    MsgBox "Sheet is now UnProtected"
   
    ActiveWorkbook.Connections("Query - MediaSubMedia").Refresh
    ActiveSheet.CircleInvalid
    ActiveWorkbook.Connections("Query - Media").Refresh
    ActiveWorkbook.Connections("Query - BudgetData").Refresh
    ActiveWorkbook.Connections("Query - TeamClients").Refresh
    ActiveWorkbook.Connections("Query - BaseVersionBudget").Refresh
    ActiveWorkbook.Connections("Query - F_Client_Grouping").Refresh
    ActiveWorkbook.Connections("Query - CurrVersionBudget").Refresh 
    MsgBox "Refresh Complete"
    
    Worksheets("BudgetInput").Range("G3:G150").Locked = False
    Worksheets("BudgetInput").Protect Password:="password"
    MsgBox "Sheet is now Protected"
End Sub

I have Message boxes to tell when each part completes. But when i get to the last message, "Sheet is now protected" i get the following message.

The cell or chart you're trying to change is on a protected sheet. To make a change, un-protect the sheet.

It looks like the Refreshes are not completing before i protect my sheet. Is there a way i can make sure my refreshes are complete, then run the code to protect my sheet.??

Kind Regards

Rob

https://stackoverflow.com/questions/22083668/wait-until-activeworkbook-refreshall-finishes-vba — simple-solution, Aug 05 '20 at 19:02
https://www.myonlinetraininghub.com/excel-forum/vba-macros/pause-macro-until-power-queries-finished-refreshing — simple-solution, Aug 05 '20 at 19:02
This worked for me ActiveWorkbook.Save forces the refreshing to complete before saving. — Rob, Aug 06 '20 at 09:54

score -1 · Answer 1 · answered Aug 05 '20 at 19:53

-1

Use this command -Application.Wait("0.010") before msgbox "refresh complete" to add wait time (eg 10seconds here) so that macro gets appropriate time to refresh. Hope this would work for you.

answered Aug 05 '20 at 19:53

Anmol Kumar

157
1
7

Excel VBA not completing my code before next line

1 Answers1