Locking Cells

[wiehan1981]

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.

[vba]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[/vba]

[Simon Lloyd]

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]

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


[VBA]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
[/VBA]

Then you need to set up the Unprotect code

[VBA]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[/VBA]

Now what you need to do is to call those from your main code.

[VBA]

sub Main_routine

call Unprotect_Me

'your code here

call Protect_Me

[/VBA]

Hope it helps

[wiehan1981]

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


[vba]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
[/vba]

Then you need to set up the Unprotect code

[vba]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[/vba]

Now what you need to do is to call those from your main code.

[vba]

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

[/vba]

Hope it helps

[fredlo2010]

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.

[VBA]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
[/VBA]

Then this would go in the change even of your worksheet

[VBA]
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[/VBA]

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

[VBA]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
[/VBA]