View Full Version : Locking Cells
wiehan1981
06-07-2012, 06:14 AM
Hi everyone,
I have a userform that records data to a worksheet. on the userform you can recall specific data for editing purposes. Now my question is, I have figured out the code (through my very limited knowledge of code writing) how to lock the cell after I have entered the data onto the worksheet, but if I recall the data back to my userform and try to edit it my PC gets mad and won't allow me to. Can this be overcome. Here is the code that I have, did I enter it correctly?
Thanx guys.
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
If Not Intersect(Target, Range("B4:B33")) Is Nothing Then 'set your range here
ActiveSheet.Unprotect Password:="pass"
Target.Locked = True
ActiveSheet.Protect Password:="pass"
End If
End Sub
Simon Lloyd
06-07-2012, 09:28 AM
Well that looks correct but thats not for the userform!, you'd have to uprotect the sheet in the userform code and then reprotect it after the operation in the userform.
fredlo2010
06-08-2012, 06:53 AM
Hi,
My knowledge here is very limited too. You need to put a protection/unprotection code and then call it before and after the procedure.
To protect
Sub Protect_Me()
With ActiveWorkbook
.Sheets("Sheet1").Protect Password:="My Password", AllowFormattingColumns:=True
'.Protect Password:="My Password", Structure:=True, Windows:=False ' <==== if you want to protect the workbook too then use this
End With
End Sub
Then you need to set up the Unprotect code
Sub Unprotect_Me()
With ActiveWorkbook
.Sheets("Sheet1").Unprotect Password:="My Password"
'.Unprotect Password:="My Password" ' <==== if you want to protect the workbook too then use this
End With
End Sub
Now what you need to do is to call those from your main code.
sub Main_routine
call Unprotect_Me
'your code here
call Protect_Me
Hope it helps
wiehan1981
06-09-2012, 02:05 AM
just so that I understand correctly,
please see in red below:
Hi,
My knowledge here is very limited too. You need to put a protection/unprotection code and then call it before and after the procedure.
To protect
Sub Protect_Me()
With ActiveWorkbook
.Sheets("Sheet1").Protect Password:="My Password", AllowFormattingColumns:=True
'.Protect Password:="My Password", Structure:=True, Windows:=False ' <==== if you want to protect the workbook too then use this
End With
End Sub
Then you need to set up the Unprotect code
Sub Unprotect_Me()
With ActiveWorkbook
.Sheets("Sheet1").Unprotect Password:="My Password"
'.Unprotect Password:="My Password" ' <==== if you want to protect the workbook too then use this
End With
End Sub
Now what you need to do is to call those from your main code.
sub Main_routine
call Unprotect_Me
something like this
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then
Exit Sub
If Not Intersect(Target, Range("B4:B33"))
Is Nothing Then
'set your range here ActiveSheet.Unprotect Password:="pass"
Target.Locked = True
ActiveSheet.Protect Password:="pass" End If End Sub
call Protect_Me
Hope it helps
fredlo2010
06-09-2012, 06:47 AM
Its hard to understand what you want or what you have. I think you have things in different places. Here is what I think you are doing.
This code will go in a module. Please note that there are two procedures in the module.
Sub Protect_Me()
With ActiveWorkbook
.Sheets("Sheet1").Protect Password:="My Password", AllowFormattingColumns:=True
'.Protect Password:="My Password", Structure:=True, Windows:=False ' <==== if you want to protect the workbook too then use this
End With
End Sub
Sub Unprotect_Me()
With ActiveWorkbook
.Sheets("Sheet1").Unprotect Password:="My Password"
'.Unprotect Password:="My Password" ' <==== if you want to protect the workbook too then use this
End With
End Sub
Then this would go in the change even of your worksheet
Private Sub Worksheet_Change(ByVal Target As Range)
Call Unprotect_Me
If Target.Cells.Count > 1 Then
Exit Sub
If Not Intersect(Target, Range("B4:B33")) Is Nothing Then
Target.Locked = True
End If
Call Protect_Me
End Sub
If you want to put everything together in one place (not recommended, that makes harder to debug or modify your code later on)
you can try this
Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveWorkbook
.Sheets("Sheet1").Unprotect Password:="My Password"
If Target.Cells.Count > 1 Then
Exit Sub
If Not Intersect(Target, Range("B4:B33")) Is Nothing Then
Target.Locked = True
End If
.Sheets("Sheet1").Protect Password:="My Password", AllowFormattingColumns:=True
End With
End Sub
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.