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

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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

Code:
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
 
Last edited:

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

Jeffrey Mahoney

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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


Code:
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
Code:
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
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.

Code:
 Set sht = ActiveSheet
You could use Me instead of using a worksheet variable.

Code:
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

Well-known Member
Joined
May 31, 2015
Messages
1,830
Office Version
  1. 365
Platform
  1. Windows
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
Code:
Public SheetsProtected As String

In "ThisWorkbook" module
Code:
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

MrExcel MVP
Joined
May 7, 2008
Messages
21,779
Office Version
  1. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,122,469
Messages
5,596,309
Members
414,052
Latest member
Dual Showman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top