UnProtect do a PQ refresh ReProtect

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
375
Office Version
  1. 365
Platform
  1. 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

Quick Sum
Select a range of cells. The total appears in bottom right of Excel screen. Right-click total to add Max, Min, Count, Average.

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
375
Office Version
  1. 365
Platform
  1. 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
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You should make sure all your queries are set to not refresh in the background.
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
375
Office Version
  1. 365
Platform
  1. 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
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

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

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,066
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Curious. Try using:

Code:
  ActiveWorkbook.RefreshAll
Application.CalculateUntilAsyncQueriesDone
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
375
Office Version
  1. 365
Platform
  1. Windows
where would i put this?
just like you did after refreshall?

what would it do?
 

rjmdc

Active Member
Joined
Apr 29, 2020
Messages
375
Office Version
  1. 365
Platform
  1. 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,122,472
Messages
5,596,353
Members
414,060
Latest member
hermanseck

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top