Lock cell in Excel worksheet with VB
Solved/Closed
MartynP
Martynp - Nov 19, 2014 at 08:38 AM
- Posts
- 5
- Registration date
- Thursday November 13, 2014
- Status
- Member
- Last seen
- March 17, 2015
Martynp - Nov 19, 2014 at 08:38 AM
Related:
- Lock cell in Excel worksheet with VB
- Lock cells in Excel ✓ - Forum - Excel
- In ms excel, if you want to insert the same formula in a given cell of different worksheets then which of the following action y - Guide
- Disable a cell in excel using VB code ✓ - Forum - Excel
- How to Lock or password protected specific cell in excel 2010 ✓ - Forum - Excel
- How to lock or unlock cells based on values in another cell in excel? ✓ - Forum - Excel
2 replies
rizvisa1
Nov 15, 2014 at 06:47 AM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
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
Nov 17, 2014 at 08:19 AM
- Posts
- 5
- Registration date
- Thursday November 13, 2014
- Status
- Member
- Last seen
- March 17, 2015
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
Nov 19, 2014 at 06:54 AM
- Posts
- 4479
- Registration date
- Thursday January 28, 2010
- Status
- Contributor
- Last seen
- May 5, 2022
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