VBA to unlock worksheet when clicking on "refresh all" ribbon button so it can refresh a power query source

[AT_TreeFortConsulting]

I'm looking for a way to use the "refresh all" ribbon button to update my power query that's in a protected sheet. I found code that works when opening the workbook, but am getting an error when trying to refresh all in the protected sheet.

The worksheet name is "Daily Activity"
The query name in the protected sheet is "daily_activity_detailed_AEI_all"

Goal: When pressing "refresh all" button in ribbon under the data tab:

• Daily Activity sheet is unprotected
• All connections are refreshed
• Daily Activity sheet is protected again

I keep getting error stating sheet is protected and query can't be refreshed without unprotecting it.

Current VBA code (in workbook module) for both the "refresh all" and for when I open the workbook (the open workbook is working correctly):

Option Explicit
Private WithEvents QT As QueryTable

Private Sub QT_AfterRefresh(ByVal Success As Boolean)
    If Success Then
    Sheets("Daily Activity").Unprotect
    ActiveWorkbook.Connections("Query - daily_activity_detailed_AEI_all").Refresh

    End If
End Sub

Private Sub Workbook_Open()
    Sheets("Daily Activity").Unprotect
    ActiveWorkbook.Connections("Query - daily_activity_detailed_AEI_all").Refresh
    Sheets("Daily Activity").Protect
End Sub

 

[PeteWright]

Well, if your code works well when opening a workbook, just place it in your Personal Workbook and assign a new custom button to it somewhere in the Ribbon.

For example:

Public Sub RefreshAllQueries()
    Sheets("Daily Activity").Unprotect
    ActiveWorkbook.Connections("Query - daily_activity_detailed_AEI_all").Refresh
    Sheets("Daily Activity").Protect
End Sub

As far as I know the built-in buttons cannot be modified, so you'll ned a custom button.

If you don't know how to do it, here's an example: