PDA

View Full Version : [SOLVED] Re-lock a Column after its been unlocked upon cell exit



simora
03-26-2016, 07:43 PM
I have a worksheet that I've unlocked a column (A), so that the user can enter an x in a specific row, however, I want that column to be locked again once any row in Column (A) is changed.
How can I re-lock column (A) once a change is made.
This is what I've tried amoung other things that will not work.
Column A is Unlocked, but the rest of the sheet is still protected at this point.



Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A65536")) Is Nothing Then
ActiveSheet.Range("A2:A65536").Locked = True
ActiveSheet.Protect
End If
End Sub


I keep getting this Error:
unable to set the locked property of the range class 1004

rollis13
03-27-2016, 01:49 AM
You need to UnProtect the sheet before changing the cell property (or format).
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A2:A65536")) Is Nothing Then
ActiveSheet.Unprotect
ActiveSheet.Range("A2:A65536").Locked = True
ActiveSheet.Protect
End If
End Sub

simora
03-29-2016, 05:44 PM
Hi rollis13 (http://www.vbaexpress.com/forum/member.php?50133-rollis13)
http://www.vbaexpress.com/forum/images/statusicon/user-offline.png
Thanks, that code works. I thought I had posted a THANK YOU note earlier, but didn't see it showing up.

rollis13
03-30-2016, 07:06 AM
Glad I was able to help :hi:.