PDA

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