Lock cell in Excel worksheet with VB
Solved/Closed
MartynP
Posts
5
Registration date
Thursday November 13, 2014
Status
Member
Last seen
March 17, 2015
-
Nov 13, 2014 at 09:34 AM
Martynp - Nov 19, 2014 at 08:38 AM
Martynp - Nov 19, 2014 at 08:38 AM
Related:
- Lock cell in Excel worksheet with VB
- Transfer data from one excel worksheet to another automatically - Guide
- Number to words in excel - Guide
- Caps lock reversed - Guide
- How to take screenshot in excel - Guide
- Marksheet in excel - Guide
2 responses
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Nov 15, 2014 at 06:47 AM
Nov 15, 2014 at 06:47 AM
I have not really tested this one. But i think it should work for u
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rowId As Long
If (Intersect(Target, Range(Cells(1, "H"), Cells(1000, "H"))) Is Nothing) Then
Exit Sub
End If
ActiveSheet.Unprotect ("")
For Each Row In Target.Rows
rowId = Row.Row
Range(Cells(rowId, "A"), Cells(rowId, "H")).Locked = (Cells(rowId, "H") = "Y")
Next
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
End Sub
MartynP
Posts
5
Registration date
Thursday November 13, 2014
Status
Member
Last seen
March 17, 2015
Nov 17, 2014 at 08:19 AM
Nov 17, 2014 at 08:19 AM
Hi rizvisa1
I think I have sorted this, just going to do some testing.
Many Thanks
I think I have sorted this, just going to do some testing.
Many Thanks
rizvisa1
Posts
4478
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
May 5, 2022
766
Nov 19, 2014 at 06:54 AM
Nov 19, 2014 at 06:54 AM
Works for me. Only thing that i can think of is that you may have "y" instead of "Y". in that case you can change the line
(Cells(rowId, "H") = "Y")
to
(UCase(Cells(rowId, "H")) = "Y")
(Cells(rowId, "H") = "Y")
to
(UCase(Cells(rowId, "H")) = "Y")
Nov 17, 2014 at 06:29 AM
Thanks