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

 

[Andrew Poulsom]

You can't do this:

[D7:V46].Locked = True And [C7:C46].Locked = False

And is a logical operator, not a word to join two statements. Try:

[D7:V46].Locked = True
[C7:C46].Locked = False

 

[NonniKcaM]

Thanks for your reply Andrew. That was a rather silly mistake, but I'm afraid it has not completely fixed the code. Can you or anyone else see any other errors? Also would it be beneficially to use a loop to call this statement so it constantly checks to see if its been changed?

Nonn

 

[Andrew Poulsom]

What do you mean by "it has not completely fixed the code"?

 

[NonniKcaM]

Meaning that the code still doesn't give the desired result (locking the cells when the condition is met)

 

[NonniKcaM]

Bump

 

[Andrew Poulsom]

What's your code now?

 

[NonniKcaM]

Updated code looks like this:

Private Sub Lock_Change(ByVal Target As Range)

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

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

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

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

End If

End Sub

Nonn

 

[Andrew Poulsom]

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

Private Sub Worksheet_Change(ByVal Target As Range)

 

[NonniKcaM]

Thanks Andrew that fixed it! Never programmed using Visual Basics before so making a lot of amateur errors. Cheers for your time and have a good day