Can no longer refresh data in a Protected Workbook since Excel version 1803

Hi Brian,

Sure, happy to. I'm no Excel expert but put this together this afternoon. Use at your own risk. 

Add this to the workbook macro section:

Private Sub Workbook_Open()

Call RefreshQuery

End Sub

Add this to a module:

Sub RefreshQuery() 

ThisWorkbook.Unprotect strAdminPassword
Application.EnableEvents = False

'This value check looks at a formula I have in the workbook. if you haven't refreshed within a week you get a 1 
If Range("SECTOR!B33") = 1 Then

    StartTime = Time
                
    ' Show 'Updating..'-message Not sure if the timer is needed any more, it was legacy from something else but it works with it, so hey if it ain't broke... 
    ActiveSheet.Shapes.Range(Array("Rectangle: Rounded Corners 1")).Visible = True
    nSec = 0.1 + Timer
    While nSec > Timer
    DoEvents
    Wend
    
   'Change "Connection" to the connection name you have.
    ActiveWorkbook.Connections("Connection").Refresh
    
    ' Remove 'Updating..'-message
    ActiveSheet.Shapes.Range(Array("Rounded Rectangle 1")).Visible = False
    nSec = 0.1 + Timer
    While nSec > Timer
    DoEvents
    Wend
    
End If

'This section below is more specific to my use case - so I track when this was last refreshed, if it's older than a week I'll force the user to refresh to continue to use the workbook. 
Dim cell As Range
Dim rng As Range
Dim RangeName As String
Dim CellName As String

'Single Cell Reference (Workbook Scope)
  RangeName = "REFRESH_DATE"
  CellName = "O1"
  
  Set cell = Worksheets("UCON Pricing Tool (2016)").Range(CellName)
  ThisWorkbook.Names.Add Name:=RangeName, RefersTo:=cell




Range("REFRESH_DATE").Value = Date
Range("REFRESH_DATE").NumberFormat = "dd/mm/yyyy"
Application.EnableEvents = True


ThisWorkbook.Unprotect strAdminPassword

End Sub

You may need to edit the code to get what you want from it, but it's a base.  I am still testing this with my workbooks so it may not be final.  Effectively, use at your own risk/understanding. It may work for you, it may not.  Currently, it seems to work for me but it's a pain in the arse of a workaround. 

You'll need to disable background refresh for your connection, and disable refresh on opening. 

David

Be aware, with a macro solution, that you may have to remove all checkboxes for 'refresh automatically when cell value changes ' for all queries in your workbook, and to refresh these queries with a command well from your macro. Similarly you will need to disable 'Refresh data when opening the file' and possibly also for background refresh. It is really a pain.