Locking sheet not working as intended

Question

I want to lock/unlock a given sheet based on the value of a cell in another sheet. I wrote the following code in worksheet change for sheet form:

Application.ScreenUpdating = False
Status = Sheets("form").Range("J2")
If Status = "Active" Then
    Sheets("overview").Unprotect "password"
    'MsgBox "The template is now unlocked"
Else
    Sheets("overview").Protect "password"
    'MsgBox "The template is locked"
End If
Application.ScreenUpdating = True

However, when Status is other than active I can still change some cells in overview getting a "Unable to set the hidden property of the Range class" popup. Where is the error?

where do you have this code written ? in which module or Worksheet event do you have it ? I just tested it and it works on my Excel Workbook. — Shai Rado
try the code below, it's working when i'm changing the value of "J2" cell in form worksheet. — Shai Rado
I don't know if it is pertinent, but the value in J2 can be selected from a drop down list. — L.Dutch

Shai Rado Shai Rado · Accepted Answer · 2016-07-13T05:31:28

Try the following code on Worksheet_Change event (in your form worksheet code) :

Private Sub Worksheet_Change(ByVal Target As Range)

    Application.ScreenUpdating = False

    Status = Me.Range("J2")
    If Status = "Active" Then
        Sheets("overview").Unprotect "password"
        'MsgBox "The template is now unlocked"
    Else
        Sheets("overview").Protect "password"
        'MsgBox "The template is locked"
    End If
    Application.ScreenUpdating = True


End Sub

Locking sheet not working as intended

1 Answers