Multiple If Statement to Lock Group of Cells

[NonniKcaM]

Hi there,

Wonder if anyone can help me with this issue. I've written a piece of code to lock cells AND unlock cells on a condition. For example if D5 = 1, users will only be able to input into the first group of cells (when protected) so the rest will be locked. This is purely to allow users to tab through everything easily. Originally the code worked when it only locked cells but I then encountered a problem where once a cell was locked it would not unlock if another condition was now met,

Hope this made some sense...

Thanks a lot

Nonn

Code bellow in short form (there is actually 19 conditions but only showing first 3):

Private Sub Lock_Change(ByVal Target As Range)

If [D5] = 1 Then
ActiveSheet.Unprotect ("password")
[D7:V46].Locked = True And [C7:C46].Locked = False
ActiveSheet.Protect ("password")

ElseIf [D5] = 2 Then
ActiveSheet.Unprotect ("password")
[E7:V46].Locked = True And [C7:D46].Locked = False
ActiveSheet.Protect ("password")

ElseIf [D5] = 3 Then
ActiveSheet.Unprotect ("password")
[F7:V46].Locked = True And [C7:E46].Locked = False
ActiveSheet.Protect ("password")

Else
ActiveSheet.Unprotect ("password")
[D7:V46].Locked = False
ActiveSheet.Protect ("password")

End If
End Sub

 

[larrycav]

And in what way isn't that working? It looks like the first line should be:

Private Sub Worksheet_Change(ByVal Target As Range)

Hmmmm... I have a similar problem with some code and I already have a sub named Private Sub worksheet_change(ByVal target As Range). How do you get around that situation ?? I tried renaming the first one and it breaks it. I need to have two...somehow?

 

[Andrew Poulsom]

You can have only one Worksheet_Change event procedure. All your code need to go in the single instance. I can't imagine why it would be necessary to have two.