Locking of cells after not working

[Dazzatron]

Hi all,

Hopefully you can help.
Im continuing on from another colleague.

I have forms at work that need locking once you put your name at the end of each line, then depending on type of job will need same for block or blocks of cells on same form.

The codes i have are (for for 1 column & multiple blocks of cells)

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("C40,G40,K40,L:L")) Is Nothing And Target <> "" Then Exit Sub
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Dim pwd As String, fnd As Range, list As Worksheet
    Set list = Sheets("Sheet1 (2)")
    Select Case Target.Column
        Case Is = 3
            Set fnd = list.Range("AJ:AJ").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            pwd = Application.InputBox("Password for " & Target & ":", "Enter Password", Type:=2)
            If pwd = fnd.Offset(, 2) Then
                ActiveSheet.Unprotect "123"
                Target.Offset(1) = fnd.Offset(, 1)
                Range("C26:E41").Locked = True
                ActiveSheet.Protect "123"
                ActiveSheet.EnableSelection = xlUnlockedCells
            Else
                Target.Nothing
                MsgBox ("Invalid password.  Please try again.")
            End If
        Case Is = 6
            Set fnd = list.Range("AJ:AJ").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            pwd = Application.InputBox("Password for " & Target & ":", "Enter Password", Type:=2)
            If pwd = fnd.Offset(, 2) Then
                ActiveSheet.Unprotect "123"
                Target.Offset(1) = fnd.Offset(, 1)
                Range("G26:H41").Locked = True
                ActiveSheet.Protect "123"
                ActiveSheet.EnableSelection = xlUnlockedCells
            Else
                Target.Nothing
                MsgBox ("Invalid password.  Please try again.")
            End If
        Case Is = 10
            Set fnd = list.Range("AJ:AJ").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            pwd = Application.InputBox("Password for " & Target & ":", "Enter Password", Type:=2)
            If pwd = fnd.Offset(, 2) Then
                ActiveSheet.Unprotect "123"
                Target.Offset(1) = fnd.Offset(, 1)
                Range("K26:L41").Locked = True
                ActiveSheet.Protect "123"
                ActiveSheet.EnableSelection = xlUnlockedCells
            Else
                Target.Nothing
                MsgBox ("Invalid password.  Please try again.")
            End If
        Case Is = 11
            Set fnd = list.Range("AK:AK").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            pwd = Application.InputBox("Password for " & fnd.Offset(, -1) & ":", "Enter Password", Type:=2)
            If pwd = fnd.Offset(, 1) Then
                ActiveSheet.Unprotect "123"
                Target.Copy
                Target.Offset(1).PasteSpecial xlPasteValidation
                Range("L" & Target.Row).Resize(, 7).Locked = True
                ActiveSheet.Protect "123"
                ActiveSheet.EnableSelection = xlUnlockedCells
            Else
                Target.ClearContents
                MsgBox ("Invalid password.  Please try again.")
            End If
    End Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

and (for 1 column and 1 block of cells

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.CountLarge > 1 Then Exit Sub
    If Intersect(Target, Range("C40,L:L")) Is Nothing And Target <> "" Then Exit Sub
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    Dim pwd As String, fnd As Range, list As Worksheet
    Set list = Sheets("Sheet1 (2)")
    Select Case Target.Column
        Case Is = 3
            Set fnd = list.Range("AJ:AJ").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            pwd = Application.InputBox("Password for " & Target & ":", "Enter Password", Type:=2)
            If pwd = fnd.Offset(, 2) Then
                ActiveSheet.Unprotect "123"
                Target.Offset(1) = fnd.Offset(, 1)
                Range("C26:E41").Locked = True
                ActiveSheet.Protect "123"
                ActiveSheet.EnableSelection = xlUnlockedCells
            Else
                Target.Nothing
                MsgBox ("Invalid password.  Please try again.")
            End If
            Else
                Target.Nothing
                MsgBox ("Invalid password.  Please try again.")
            End If
        Case Is = 11
            Set fnd = list.Range("AK:AK").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
            pwd = Application.InputBox("Password for " & fnd.Offset(, -1) & ":", "Enter Password", Type:=2)
            If pwd = fnd.Offset(, 1) Then
                ActiveSheet.Unprotect "123"
                Target.Copy
                Target.Offset(1).PasteSpecial xlPasteValidation
                Range("L" & Target.Row).Resize(, 7).Locked = True
                ActiveSheet.Protect "123"
                ActiveSheet.EnableSelection = xlUnlockedCells
            Else
                Target.ClearContents
                MsgBox ("Invalid password.  Please try again.")
            End If
    End Select
    Application.EnableEvents = True
    Application.ScreenUpdating = True
End Sub

but its not working, i did somehow get C26:E41 and G26:H41 in the first example to work somehow but now wont.
I have set the cells i allow for editing as unlocked and the rest locked.
I am new to VBA so have minimal understanding of it. Especially the "case is=" part

I am unable to post copies of these workbooks but can answer your questions as best as allowed to to asist in my quest.

Any help would be much appreciated, this is started to give me a headache lol

 

[mumps]

You could take your actual sheet and just replace any confidential data with some dummy data. The important thing is not to change the way the data is organized. Then you could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here.

 

[Dazzatron]

Done as suggested

Dropbox

www.dropbox.com

Hope this works.
Thanks