Hi All,

I have an excel workbook in which there are three sheets, each sheet has two cells which I have named as Sheet1!Sign1, Sheet1!Sign2.... Sheet3!Sign2.

What I intend to do is to lock the sheet whenever the signature cells are filled in for a particular sheet.

Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)

    
Dim NamesList
Dim x As Integer
Dim SignStatus1, SignStatus2 As Boolean
    
For x = 1 To ActiveWorkbook.Worksheets.Count
    For y = 1 To ActiveWorkbook.Worksheets(x).Names.Count
        NamesList = ActiveWorkbook.Worksheets(x).Names(y).Name
        If InStr(1, NamesList, "Sign1", vbTextCompare) <> 0 And ActiveWorkbook.Worksheets(x).Names(NamesList).Value = 0 Then
                
                SignStatus1 = False
        Else
               'ActiveWorkbook.Worksheets(x).Range(NamesList).Locked = False
                SignStatus1 = True
        End If
        
        If InStr(1, NamesList, "Sign2", vbTextCompare) <> 0 And ActiveWorkbook.Worksheets(x).Names(NamesList).Value = 0 Then
                SignStatus2 = False
        Else
                'ActiveWorkbook.Worksheets(x).Range(NamesList).Locked = False
                SignStatus2 = True
        End If
    Next y
    
        If SignStatus1 = True And SignStatus2 = True Then
            ActiveWorkbook.Worksheets(x).Protect
        Else
            ActiveWorkbook.Worksheets(x).Unprotect
        End If
Next x
The overall intention is that when someone fills in the signature cells the sheet gets locked or protected and whenever the signs are deleted only then data can be edited

With the code above the sheet gets protected but I want to leave the sign cells unprotected.

Two commented lines in the code return an error

Unable to set the locked property of range class to locked

Can someone please help

Thanks

Mujahid