Excel 2013 VBA wsheet.protect UserInterfaceOnly:=True

Question

I ran a simple sub called SetProtection() one time on a macro enabled template. The workbook has 9 tabs. I ran the code so that UserInterfaceOnly:=True. It works fine while the template is open. I save and close the template. From a windows folder view, I double click the template file so that excel opens a new workbook. The UserInterfaceOnly:=True setting is forgotten by the new workbook. I get an error when the value of a cell is changed when vba tries to change the value of another cell which is locked.

Lets say I edit the template file and savAs a macro enabled workbook. If I edit the workbook the UserInterfaceOnly:=True setting continues to work. VBA can make changes to locked cells.

Shouldn't UserInterfaceOnly:=True be passed on by an excel template file?

Sub SetProtection()

    Dim wSheet          As Worksheet
    Dim Pwd             As String
 Pwd = InputBox("Enter your password to protect all worksheets", "Password Input")
    For Each wSheet In Worksheets
        wSheet.Protect Password:=Pwd, UserInterfaceOnly:=True
    Next wSheet
End Sub

Tim Williams Tim Williams · Accepted Answer · 2016-05-24T16:45:40

See: http://www.cpearson.com/excel/Protection.aspx

The UserInterfaceOnly setting is not saved when you close the workbook, so you need to set it when the workbook is opened. The best place to do this is in the Workbook_Open event procedure.

Excel 2013 VBA wsheet.protect UserInterfaceOnly:=True

1 Answers