Using VBA to unprotect a sheet that has been protected with UserInterface Only = true

Question

I have an issue with worksheet protection at the moment. I have read through this forum and found that UserInterfaceOnly = true is useful to avoid having to unprotect the sheet, enter the code and re-protect.

However, I have a requirement to unprotect some sheets by using a macro (there are people with additional access to amend some worksheets that I don't want to know the main password), but the macro doesn't unprotect the sheet.

I am using the following code in the workbook to protect and hide sheets on opening.

Private Sub Workbook_Open()
    'unprotect workbook'
    Application.ScreenUpdating = False

    On Error Resume Next
    ActiveWorkbook.Unprotect Password:="PASSWORD"

    'Hide all worksheets except Project info and requisition. Protect all worksheets except template - but allowing macros to work while protected'
    Dim sheet As Worksheet
    For Each sheet In Worksheets
        If sheet.Name <> "Project Info" And sheet.Name <> "Requisition" And sheet.Name <> "Template" Then sheet.Visible = xlSheetHidden
        If sheet.Name <> "Template" And sheet.Name <> "Task Controls" Then sheet.Protect Password:="PASSWORD", DrawingObjects:=True, Contents:=True, Scenarios:=True _
            , UserInterfaceOnly:=True, AllowFiltering:=True
        If sheet.Name <> "Template" Then sheet.EnableSelection = xlUnlockedCells
    Next

    ActiveWorkbook.Protect Password:="PASSWORD", structure:=True, Windows:=False

    Application.ScreenUpdating = True

    End Sub

This works fine for the vast majority of the workbook (and for which I owe thanks to this forum), however when I use the following code to unprotect a sheet to allow it to be edited, the sheet does not unprotect. Note that this is used from the OK button of a Userform if that makes a difference

Private Sub OK_Button_Click()

    Dim Supplier As String
    Dim ws1 As Worksheet
    Dim ws2 As Worksheet
    Supplier = Me.Supplier_ComboBox.Value
    Set ws1 = Sheets("Buyer's Sheet")
    Set ws2 = Sheets(Supplier)

    On Error Resume Next
    ActiveWorkbook.Unprotect Password:="PASSWORD"
    ws2.Visible = xlSheetVisible
    ActiveWorkbook.Protect Password:="PASSWORD", structure:=True, Windows:=False

    ws2.Select
    Set ws2 = ActiveSheet
    ws2.Columns.Hidden = False
    ws2.Unprotect Password:="PASSWORD"

    MsgBox "Make the required amendments to the Price List and click the button to return to the home screen", vbOKOnly, "Amend Price List"

    Dim ctl As Control

    For Each ctl In Me.Controls
        If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
            ctl.Value = ""
        ElseIf TypeName(ctl) = "CheckBox" Then
            ctl.Value = False
        End If
    Next ctl

    Unload Me

    End Sub

Some of the code is probably fairly inefficient (particularly use of .select) but it appears to work and be pretty stable. The only function that does not work is the worksheet.unprotect function.

Remove the On Error Resume Next and see what error message it's giving you. There are very few places where On Error Resume Next is useful, and I don't see this being one of them - all you're doing is ignoring the errors in your code, it's not fixing anything. — FreeMan
I have removed all instances of this but it has not had an effect. The open workbook script appears to be working as designed as all sheets are visible/hidden and protected/unprotected as required. The issue seems to be with the unprotect function in the second piece of code. Could it be that the UserInterfaceOnly option being enabled on the initial protection is invalidating the unprotect? The only other thing I can think of is that 'unloading' the userform has negated the unprotect in some way. There is no error message at any stage though — Private Slippers

FreeMan FreeMan · Accepted Answer · 2015-05-12T12:21:34

Looking more closely at your code after a night's sleep, I believe I see the issue. You're unprotecting the Workbook, then immediately re-protecting it, then trying to unprotect a Worksheet within the protected Workbook. Not 100% certain that's an issue, but try this:

Private Sub OK_Button_Click()

'Dim Supplier As String
Dim ws1 As Worksheet
Dim ws2 As Worksheet

Set ws1 = Sheets("Buyer's Sheet")
Set ws2 = Sheets(Me.Supplier_ComboBox.Value)

On Error Resume Next
ActiveWorkbook.Unprotect Password:="PASSWORD"
ws2.Visible = xlSheetVisible
'remove this line to leave the workbook unptrotected while you're using it
'ActiveWorkbook.Protect Password:="PASSWORD", structure:=True, Windows:=False

'ws2.Select
'Set ws2 = ActiveSheet
'note switched order of next two lines - unprotect FIRST
ws2.Unprotect Password:="PASSWORD"  
ws2.Columns.Hidden = False

MsgBox "Make the required amendments to the Price List and click the button to return to the home screen", vbOKOnly, "Amend Price List"

Dim ctl As Control

For Each ctl In Me.Controls
    If TypeName(ctl) = "TextBox" Or TypeName(ctl) = "ComboBox" Then
        ctl.Value = ""
    ElseIf TypeName(ctl) = "CheckBox" Then
        ctl.Value = False
    End If
Next ctl

Unload Me

End Sub

Now, based on the text of your MsgBox, it looks like you need to leave the sheet unprotected for the user to make some changes, then (s)he clicks another button that re-protects and does some other processing. I believe that this is the issue - your worksheet is protected, but the workbook protection is overriding it. Again, I'm not 100% certain on this, and the Workbook protection documentation and Worksheet protection documentation aren't very clear (to me) about this.

My changes (cleaned up a couple of unnecessary lines near the top), leaves the workbook and the worksheet unprotected so the user can make changes. I'm assuming that the button click referred to in the MsgBox does, or will be modified to, re-protect everything.

Using VBA to unprotect a sheet that has been protected with UserInterface Only = true

1 Answers