Prevent Worksheet Protection

  • I have a workbook for which I would like to protect the worksheets, while still allowing my code to alter the worksheets, which can be done with the line:


    Code
    Sheet1.Protect Password="abc" UserInterfaceOnly:=True


    However, I want allow some users (who know the password) to be able to unprotect the sheet if they need to edit it, then turn the protection back on after they are done. How can I make sure that they use my macro to protect the sheet with UserInterfaceOnly set to true, rather than the standard way to turn on protection?


    That is, is there a way I can prevent the user from being able to protect the sheet with the Tools->Protection->Protect Sheet menu item?


    Thanks

  • Re: Prevent Worksheet Protection


    Code
    Application.CommandBars.FindControl(ID:=893).Enabled = False


    Or, in ThisWorkbook Module;

    Code
    Private Sub Workbook_Activate()
    Application.CommandBars(1).Controls("Tools").Controls _
    ("Protection").Controls("Protect Sheet...").Enabled = False
    End Sub
    
    
    Private Sub Workbook_Deactivate()
    Application.CommandBars(1).Controls("Tools").Controls _
    ("Protection").Controls("Protect Sheet...").Enabled = True
    End Sub

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!