Sheet protection Issue upon reopening

[ABabeNChrist]

I have a workbook that uses workbook protection

Code: Select all

    ActiveWorkbook.Protect Password:="", Structure:=True, Windows:=True

and sheet protection

Code: Select all

        Sheets("Sheet1").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                Password:="", UserInterfaceOnly:=True

The problem I seem to be having is, when I save the workbook and then close, when I reopen the workbook, which first opens to a userform, from there I select a command button that only unloads the userform, now from there if I try to continue where I left off I have to unprotect each and every sheet before using a checkbox or a command button attached to code. Is there any type of code I could add with the unload userform code that can correct this

[HansV]

You write "before using a checkbox or a command button attached to code" - are there controls on a sheet or on another userform?

[HansV]

Oops, I thought you already knew that you have to protect the sheets with UserInterfaceOnly:=True in the Workbook_Open event procedure in the ThisWorkbook module, e.g.

Code: Select all

Private Sub Workbook_Open()
        Sheets("Sheet1").Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                Password:="", UserInterfaceOnly:=True
End Sub

for a single sheet, or

Code: Select all

Private Sub Workbook_Open()
  Dim wsh As Worksheet
  For Each wsh In Worksheets
    wsh.Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
                                Password:="", UserInterfaceOnly:=True
  Next wsh
End Sub

for all sheets. The UserInterfaceOnly status is not saved with the workbook, so you have to set it each time the workbook is opened.

[ABabeNChrist]

Thank you Hans
I didn’t realize that the UserInterfaceOnly status could not saved with the workbook.
This is very helpful and quite simple way to reapply.
Now will this also work even if a sheet maybe hidden.