UnProtect do a PQ refresh ReProtect

[rjmdc]

hi
i am working on setting up the ability to refresh PQ on a protected sheet
this is my code:

Sub RefreshPQActiveWorkbook()

Dim ActSheet As String
ActSheet = ActiveSheet.Name


Workbooks("SD Reimbursements").Activate

If MsgBox("Do you want to refresh?", vbOKCancel + vbQuestion) = vbOK Then

    Dim ws As Worksheet

    For Each ws In ActiveWorkbook.Worksheets
        Call WSUnProtect(ws)
    Next ws
    
    DoEvents

   ActiveWorkbook.RefreshAll
    
        
    For Each ws In ActiveWorkbook.Worksheets
        Call WSProtect(ws)
    Next ws

it maybe does the refresh because i keep getting this message:
the cells you are trying to change is on a protected sheet

 

[rjmdc]

i already also have a module:

Sub WSProtect(ws As Worksheet)
    With ws
        .Protect Password:="mycodexxxx", DrawingObjects:=True, Contents:=True, Scenarios:=True
        .EnableSelection = xlUnlockedCells
    End With
End Sub

Sub WSUnProtect(ws As Worksheet)
    With ws
        .Unprotect Password:="mycodexxxx"
    End With
End Sub

 

[RoryA]

You should make sure all your queries are set to not refresh in the background.

 

[rjmdc]

they arent set and were never set to refresh in the background
i still get the messages of protected sheet

 

[RoryA]

The default for a PQ connection is to allow background refresh - did you change that?

 

[rjmdc]

yes

 

[RoryA]

Curious. Try using:

  ActiveWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone

​

 

[rjmdc]

where would i put this?
just like you did after refreshall?

what would it do?

 

[rjmdc]

hi
thanks
i realized that i just added 4 connection only queries that i didnt remove the auto refresh
everything works now
thanks