I have 12 worksheets that have four different queries that run based on the selection from a userform menu. To prevent changes I lock the sheets using the UserInterfaceOnly option of the Protect property. Everything else in my script runs fine except for the query. I believe it is because I do not refresh the data but instead I delete any old queries and add a new query to the wSheet. I know I could do this by temporarily unlocking the wSheet but I'd like to avoid that since it does leave the code open if an error occurs or someone stops the script while processing.



Sub Workbook_Open()
    AutomatedUpdate.LockSheets 'Set the password and UserInterfaceOnly option for each worksheet
    AutomatedUpdate.CheckForNightUpdate 'Checks the current time and executes different scripts based on the time of day
End Sub


Sub LockSheets()
    Dim wSheet As Worksheet
    For Each wSheet In ActiveWorkbook.Worksheets
        wSheet.Protect _
            Password:="MyPassword", _
            UserInterFaceOnly:=True
    Next wSheet
End Sub




Thanks in advance for any assistance

DaveG

I think you're stuck with this protection problem. You'll have to unprotect / protect the worksheet when running the update to get it to work.

If you're worried about leaving the sheet unprotected on unexpected program termination there are a couple of things you can do reduce the likelihood of leaving the sheet unprotected.
1) You can cut out user interferance by using Application.XlEnableCancelKey = xlDisabled (or xlErrorHandler). This will stop the user being able to interupt program flow with Ctrl + Break.
2) Introduce an error handling section such that on any error the program flow is directed to a closing clean-up section that will include the re-protection of the required sheets.

Maccas