UnProtect do a PQ refresh ReProtect

rjmdc

Board Regular
Joined
Apr 29, 2020
Messages
242
Office Version
365
Platform
Windows
hi
i am working on setting up the ability to refresh PQ on a protected sheet
this is my code:
VBA 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
 

Some videos you may like

Excel Facts

Wildcard in VLOOKUP
Use =VLOOKUP("Apple*" to find apple, Apple, or applesauce

rjmdc

Board Regular
Joined
Apr 29, 2020
Messages
242
Office Version
365
Platform
Windows
i already also have a module:
VBA Code:
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

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,063
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
You should make sure all your queries are set to not refresh in the background.
 

rjmdc

Board Regular
Joined
Apr 29, 2020
Messages
242
Office Version
365
Platform
Windows
they arent set and were never set to refresh in the background
i still get the messages of protected sheet
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,063
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
The default for a PQ connection is to allow background refresh - did you change that?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,063
Office Version
365, 2019, 2016, 2010
Platform
Windows, MacOS
Curious. Try using:

Code:
  ActiveWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
 

rjmdc

Board Regular
Joined
Apr 29, 2020
Messages
242
Office Version
365
Platform
Windows
where would i put this?
just like you did after refreshall?

what would it do?
 

rjmdc

Board Regular
Joined
Apr 29, 2020
Messages
242
Office Version
365
Platform
Windows
hi
thanks
i realized that i just added 4 connection only queries that i didnt remove the auto refresh
everything works now
thanks
 

Watch MrExcel Video

Forum statistics

Threads
1,100,032
Messages
5,472,102
Members
406,802
Latest member
Jakub3

This Week's Hot Topics

Top