Protecting Cells

[excelheadjay]

Hi,

I have a work book in which i have a number of cells, I want to be able to protected certain cells in the same row one a field contains a certain data is entered in a Cell in Column P, what i want to happen is once Column P is Changed to "Y" it looks the respective cells in columns N and O to be protected.

I am currently using this Macro

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("P:P")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (do your thing here)
If Target.Value = "Y" Then
ActiveSheet.Unprotect "PasswordGoesHere"
Range(Cells(Target.Row, "N"), Cells(Target.Row, "0")).Locked = True
ActiveSheet.Protect "PasswordGoesHere"
End If
End Sub

However this Macro Locks the entire sheet. I only want it to lock that specific ROW

I.E.

If Cell P1 is Y then the Macro should Lock N1 and O1 "ONLY".

I have tried editing the Macro as follows.

Private Sub Worksheet_Change(ByVal Target As Range)
' Code goes in the Worksheet specific module
Dim rng As Range
' Set Target Range, i.e. Range("A1, B2, C3"), or Range("A1:B3")
Set rng = Target.Parent.Range("P1")
' Only look at single cell changes
If Target.Count > 1 Then Exit Sub
' Only look at that range
If Intersect(Target, rng) Is Nothing Then Exit Sub
' Action if Condition(s) are met (do your thing here)
If Target.Value = "Y" Then
ActiveSheet.Unprotect "PasswordGoesHere"
Range(Cells(Target.Row, "N1"), Cells(Target.Row, "01")).Locked = True
ActiveSheet.Protect "PasswordGoesHere"
End If
End Sub

However this still does the same thing.

Any ideas on how to get this done ??

 

[VoG]

You need to select the entire sheet, Format > Cells > Protection tab, untick Locked.

Then your first code should work with this change

Range(Cells(Target.Row, "N"), Cells(Target.Row, "O")).Locked = True

 

[excelheadjay]

I tried that,

I get a Debug error on this line

Range(Cells(Target.Row, "N"), Cells(Target.Row, "0")).Locked = True
However after making a few adjustments

Range(Cells(Target.Row, "N"), Cells(Target.Row, "O")).Locked = True
it works like a charm

 

[VoG]

which is exactly what I told you: change 0 to O

 

[excelheadjay]

Thank you for the help.


Is it possible to use this script on a Shared spread sheet ??

If so do i need a new script ?

 

[VoG]

As far as I know it should work. The only way to find out is to try it.

 

[excelheadjay]

I tried it and i get a run time error of 1004 when i try to protect certain cells.

O FYI i am using Excel 2010..... and ideas ?

 

[excelheadjay]

Question is it possible to "lock" values in a specific cell.... ?