Combining Macro for Refreshing Queries across multiple protected sheets

Hi Im new to VBA so really have very little to go on. I found two scripts, one for refreshing queries in protected sheets and another which runs the former across multiple sheets at once i tried to put the code together as below but was met with a error regarding a "rogue end sub"

Private Sub workbook_open()
Sub Dosomething()
Dim xSh As Worksheet
Application.ScreenUpdating = False
For Each xSh In Worksheets
xSh.Select
Call RunCode
Next
Application.ScreenUpdating = True
End Sub
Sub RunCode()
'your code here
Sub DataRefresh()
'Update by Extendoffice 5/28/2019
ActiveSheet.Unprotect "123"
ActiveWorkbook.RefreshAll
Application.OnTime Now + TimeValue("00:00:01"), "DataRefresh2"
End Sub
Sub DataRefresh2()
If Application.CommandBars.GetEnabledMso("RefreshStatus") Then
Application.OnTime Now + TimeValue("00:00:01"), " DataRefresh2"
Else
ActiveSheet.Protect "123"
End If
End Sub
End Sub

I really have no idea what's gone wrong 😞

Macros and VBA

JKPieterse

Jan 17, 2023

Matt_C_Paradox If I understood well, this is what needs to be done.

This bit goes in the ThisWorkbook module:


Private Sub workbook_open()
    UnprotectAndRefresh
End Sub

And this part goes in a normal module (Insert, Module):

Sub UnprotectAndRefresh()
    Dim xSh As Worksheet
    Application.ScreenUpdating = False
    For Each xSh In Worksheets
        xSh.Unprotect "123"
    Next
    
End Sub

Sub DataRefresh()
'Update by Extendoffice 5/28/2019
    ActiveWorkbook.RefreshAll
    Application.OnTime Now + TimeValue("00:00:01"), "IsRefreshDone"
End Sub

Sub IsRefreshDone()
    Dim xSh As Worksheet
    If Application.CommandBars.GetEnabledMso("RefreshStatus") Then
        'Refresh still running, schedule another check in 1 second
        Application.OnTime Now + TimeValue("00:00:01"), " IsRefreshDone"
    Else
        'Refreshing is done, reprotect sheets
        For Each xSh In Worksheets
            xSh.Protect "123"
        Next
    End If
End Sub

JKPieterse

Silver Contributor

Jan 17, 2023

Matt_C_Paradox If I understood well, this is what needs to be done.

This bit goes in the ThisWorkbook module:


Private Sub workbook_open()
    UnprotectAndRefresh
End Sub

And this part goes in a normal module (Insert, Module):

Sub UnprotectAndRefresh()
    Dim xSh As Worksheet
    Application.ScreenUpdating = False
    For Each xSh In Worksheets
        xSh.Unprotect "123"
    Next
    
End Sub

Sub DataRefresh()
'Update by Extendoffice 5/28/2019
    ActiveWorkbook.RefreshAll
    Application.OnTime Now + TimeValue("00:00:01"), "IsRefreshDone"
End Sub

Sub IsRefreshDone()
    Dim xSh As Worksheet
    If Application.CommandBars.GetEnabledMso("RefreshStatus") Then
        'Refresh still running, schedule another check in 1 second
        Application.OnTime Now + TimeValue("00:00:01"), " IsRefreshDone"
    Else
        'Refreshing is done, reprotect sheets
        For Each xSh In Worksheets
            xSh.Protect "123"
        Next
    End If
End Sub

Matt_C_Paradox
Copper Contributor
Jan 18, 2023
Awesome! it works like a dream... I've credited you in a comment at the end 🙂

Am I right in thinking the major change you made was defining the module first then introducing what it does?
- JKPieterse
  Silver Contributor
  Jan 18, 2023
  That, and making sure each routine is on its own, starting with "Sub RoutineName" and ending with "End Sub", your example code had multiple routines "nested" in each other, that is invalid syntax.

Forum Discussion

Combining Macro for Refreshing Queries across multiple protected sheets

Share

Resources