Is there a way to permanently set Sheet Protect with userInterfaceOnly?

[Jeffrey Mahoney]

I use the userInterfaceOnly option all the time with sheet protection. It works really well without having to unprotect the sheet, run code and re-protect. The only problem is that I have to protect the sheets with this method every time the workbook is opened. That's ok if I have a smaller number of sheets that need that. With a few hundred sheets, at startup, it starts taking over a minute to protect all the sheets.

Is there a way to permanently set the protection with the userInterfaceOnly option even when the file is closed?

Jeff

With sht          '.EnableOutlining = True
          .Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
            AllowFormattingCells:=False, AllowFormattingColumns:=False, _
            AllowFormattingRows:=False, AllowInsertingHyperlinks:=False, AllowFiltering:=False, _
            userInterfaceOnly:=True, Password:="sn"
        End With

 

[Jeffrey Mahoney]

I'll assume by no answer that it can't be done.

Since it takes so long to protect a lot of sheets, I decided to take the code out of the Workbook Open Sub and put it in "ThisWorkook" module as a Sheet event. It only takes a second at a time instead of 50 seconds for 50 sheets at startup. Precarious at best.

Unless somebody has a better solution. :D

Private Sub Workbook_SheetActivate(ByVal Sh As Object)  
  Dim sht As Worksheet
  
  'If InStr(ThisWorkbook.Name, "Template") > 0 Then Exit Sub
  Set sht = ActiveSheet
  
  If SheetRangeExists(sht, "MgrRollupItm_hdr") = True Or sht.Name = "Control Panel" Then
      If sht.Name <> "Master" Then
        With sht
          '.EnableOutlining = True
          .Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
            AllowFormattingCells:=False, AllowFormattingColumns:=False, _
            AllowFormattingRows:=False, AllowInsertingHyperlinks:=False, AllowFiltering:=False, _
            userInterfaceOnly:=True, Password:="sn"
        End With
      End If
    End If
End Sub

with a helper function

Function SheetRangeExists(wks As Worksheet, sRng As String) As Boolean    
    On Error Resume Next
    SheetRangeExists = Len(wks.Range(sRng).Address)
    On Error GoTo 0
    Err.Clear
End Function

 

[shg]

I think it's clever to do it as sheets are activated. You could use a static variable to remember if it's already been done if the 1s processing time makes the workbook noticeable less responsive.

 Set sht = ActiveSheet

You could use Me instead of using a worksheet variable.

If sht.Name <> "Master" Then

There's no need to do that; don't put the code in any worksheet you don't want processed.

 

[Jeffrey Mahoney]

Good suggestion on the variable to track if it has been protected.

The code for SheetActivate is in "ThisWorkbook" instead of each individual sheet module. I have other code that I didn't post in that SUB. It changes often enough that I didn't want to update the Sheet Code hundreds of times.

In a standard module

Public SheetsProtected As String

In "ThisWorkbook" module

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
  Dim sht As Worksheet
  
  'If InStr(ThisWorkbook.Name, "Template") > 0 Then Exit Sub
  Set sht = ActiveSheet
  If InStr(SheetsProtected, "{" & sht.Name & "}") > 0 Then Exit Sub
  
  If SheetRangeExists(sht, "MgrRollupItm_hdr") = True Or sht.Name = "Control Panel" Then
      If sht.Name <> "Master" Then
        With sht
          '.EnableOutlining = True
          .Protect DrawingObjects:=False, Contents:=True, Scenarios:=True, _
            AllowFormattingCells:=False, AllowFormattingColumns:=False, _
            AllowFormattingRows:=False, AllowInsertingHyperlinks:=False, AllowFiltering:=False, _
            userInterfaceOnly:=True, Password:="sn"
        End With
        
        SheetsProtected = SheetsProtected & "{" & sht.Name & "}"
      End If
    End If
End Sub

 

[shg]

The code for SheetActivate is in "ThisWorkbook" instead of each individual sheet module.

Missed that. In that case, Sh is the object variable referencing the activated sheet.