Lock cells

[lui_roc]

I am trying to lock cells in a row depending on a value of a cell in that row. i.e. If the value in column P is greater > 1 then cells B to O (in that row) are locked.

P7 to the last used cell will be the range for the code to look for values above 1.

[Jacob Hilderbrand]

Try this code. Put it in the code section for the sheet you want it to trigger on (right click the sheet tab and select view code).


Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

Dim Cel As Range

Application.EnableEvents = False
ActiveSheet.Unprotect Password:="Pass"

For Each Cel In Target
If Cel.Column = 16 Then 'P
Range("B" & Cel.Row & ":O" & Cel.Row).Locked = Cel.Value > 1
End If
Next

ActiveSheet.Protect Password:="Pass", DrawingObjects:=True, _
Scenarios:=True, Contents:=True
Application.EnableEvents = True

End Sub

[lui_roc]

Thanks for the reply.

I've tried the code and I'm not sure we are on the same wave length. What I'm trying to do is change the properties of cells from unlocked to locked. At the moment cells B to P are unlocked and if the value in P is greater than 1 then they become locked.

Is that what the code was attempting to do?

[Jacob Hilderbrand]

What the code does is, if the value in P is changed and is >1 the cells from B:O in that row will lock, otherwise they will unlock. So, for example, if you put 0 in the cell they would unlock. Do you only want them to lock and not unlock?

[lui_roc]

I do want the code to lock and unlock. The reason it may not be working on my spreadsheet is because the values are entered automatically using a macro?

[Jacob Hilderbrand]

You probably have Application.EnableEvents = False in your other macro, so this code will not trigger. No problem though. Just call this macro at the end of your other macro.


Call LockUnlock


Option Explicit

Private Sub LockUnlock()

Dim i As Long
Dim LastRow As Long

Application.EnableEvents = False
ActiveSheet.Unprotect Password:="Pass"

LastRow = Range("B65536").End(xlUp).Row
For i = 7 To LastRow
Range("B" & Cel.Row & ":O" & Cel.Row).Locked = Range("P" & i).Value > 1
Next i

ActiveSheet.Protect Password:="Pass", DrawingObjects:=True, _
Scenarios:=True, Contents:=True
Application.EnableEvents = True

End Sub


Change "Pass" to the password you are using for the sheet. Or to "" if there is no password.

[lui_roc]

I've tried that and it came up with an error.

I have attached my spredsheet to the email for you to look at, if you don't mind. Your code is in module 5 and the macro is called in module 1. To activate module 1 select a risk source from the list box in column A (RiskRegister spreadsheet).

[smc2911]

The LockUnlock is specified to be Private, which means that it is not visible outside the module (in your case Module5). You will either need to move the code to the module from which you will be calling LockUnlock, or remove the Private keyword.

Sean.

[Jacob Hilderbrand]

Yeah, just remove Private. It was just left over from a copy / paste.

[lui_roc]

I've tried the code as suggest and I am getting another error:
"object variable or with block variable not set"

I have added "dim cel as range"

any thoughts?

[lui_roc]

Could it have anything to do with the value being calculated from a formula?

[Jacob Hilderbrand]

What line is highlighted when you debug?

[lui_roc]

Range("B" & cel.Row & ":O" & cel.Row).Locked = Range("P" & i).Value > 1

[mdmackillop]

Try
Range("B" & i & ":O" & i).Locked = Range("P" & i).Value > 1

[lui_roc]

same line is highlighted when debug, error reads "Syntax error"

[lui_roc]

Ignore last reply, I entered the code incorrectly. Thanks you all for your help.
MD that seems to of done the trick :clap:

[mdmackillop]

Here's my full code. I added the colouring just for display

Option Explicit

Sub LockUnlock()

Dim i As Long
Dim LastRow As Long

Application.EnableEvents = False
ActiveSheet.Unprotect Password:="Pass"

LastRow = Range("B65536").End(xlUp).Row
For i = 7 To LastRow
Range("B" & i & ":O" & i).Locked = Range("P" & i).Value > 1
'******************
Select Case Range("P" & i).Value > 1
Case Is = True
Range("B" & i & ":O" & i).Interior.ColorIndex = 6
End Select
'*********************
Next i

ActiveSheet.Protect Password:="Pass", DrawingObjects:=True, _
Scenarios:=True, Contents:=True
Application.EnableEvents = True

End Sub

[lui_roc]

Nice idea with the colour, I wouldn't mind using grey do you know the colour code for that

[mdmackillop]

Colour palettes can be changed
Try

Sub Colours()
For i = 1 To 56
ActiveCell.Offset(i) = i
ActiveCell.Offset(i, 1).Interior.ColorIndex = i
Next
End Sub

[lui_roc]

thanks, very useful!