Excel macro trying to make changes after macro is finished and worksheet is locked

I protect an Excel document from being edited since users keep breaking it.

While this Excel document shouldn't be broken this easily; it is outside my scope to fix it. I am here to provide a temporary solution.

The issue
When users execute the refresh macro it locks the worksheet too fast; in turn it gives an error that it can't make changes since the cell or chart is protected.
The module for locking the sheet gets executed at the end.

My Code

Sub Verversen()
' Verversen Macro
Call Module11.unlockcells
    ActiveWorkbook.Worksheets("Batch Checker").ListObjects("AllStockSQL").Sort. _
    ActiveWorkbook.Worksheets("Batch Checker").ListObjects("AllStockSQL").Sort. _
        SortFields.Add Key:=Range("AllStockSQL[[#Headers],[#Data],[Batch]]"), SortOn _
        :=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Batch Checker").ListObjects("AllStockSQL").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
    End With
'Call Module12.Refresh_All_Data_Connections
'   Range("G31").Select
'   ActiveWorkbook.RefreshAll
Call Module9.lockcells
End Sub

Module 9

Public Sub lockcells()

Worksheets("Batch Checker").Range("F4:N200").Locked = True
ActiveSheet.Protect Password:="temp", UserInterfaceOnly:=True, AllowUsingPivotTables:=True

End Sub

Module 11

Public Sub unlockcells()

ActiveSheet.UNprotect Password:="temp"
Worksheets("Batch Checker").Range("F4:N200").Locked = False

End Sub

I tried a wait timer;
I turned off the background refresh except option Refresh this connection on Refresh All;
I tried to put DoEvents before;
I tried creating different modules which I'd call in.


  • Solution was given by Emilio Silva.

    In the lock module before it locks I put Application.CalculateFull and Application.CalculateUntilAsyncQueriesDone.

    Module 9

    Public Sub lockcells()
        Worksheets("Batch Checker").Range("A4:N200").Locked = True
        Worksheets("Batch Checker").Protect Password:="REDacted", UserInterfaceOnly:=True, AllowUsingPivotTables:=True
    End Sub