PDA

View Full Version : Only locking particular cells without protecting the whole sheet?



grohm
04-17-2007, 05:57 AM
Hi Guys

Is it possible to lock particular cells (lets say A2-D2) for writing without locking the whole sheet? I am on this for some hours now and it seems that there is no real solution to this? :banghead:

Greetings,

Ben

feathers212
04-17-2007, 06:04 AM
In the Format Cells - Protection tab, unclick the "locked" checkbox for all of the cells you don't need protected. Then protect the sheet. Those cells with "Locked" left checked will then be protected.

grohm
04-17-2007, 06:19 AM
In the Format Cells - Protection tab, unclick the "locked" checkbox for all of the cells you don't need protected. Then protect the sheet. Those cells with "Locked" left checked will then be protected.

the prob with that is, that i cannot lock the sheet manually since it is already locked with a macro and it needs to be that way. so it really requires to be lock-able without using the regular sheet protection....

feathers212
04-17-2007, 06:28 AM
Can you post your workbook?

grohm
04-17-2007, 06:32 AM
Can you post your workbook?

im afraid, no. it has some data of our company which is busiiness critical and therefore i can't do that :-(

actually i only need the code to protect a cell from writing with VBA and not with the regular sheet-protection....it doesn't matter how the cell is protected, i'm just looking for a way that you can not write in it. i mean, if you have an if/else function with a password messagebox , that would be perfectly fine. i tired that but it didn't work out, but i'm pretty new to VBA so it might be me....

lenze
04-17-2007, 10:59 AM
Assuming your cells are already populated, you can use Data Validation to prevent them being changed
Custom
=""

OR , you can use VBA


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("$A$2:$D$2")) Is Nothing Then Exit Sub
Application.EnableEvents = False
MsgBox "You may not edit this cell"
Application.Undo
Application.EnableEvents = True
End Sub

lenze

lenze
04-17-2007, 11:04 AM
Assuming your cells are already populated, you can use Data Validation to prevent them being changed
Custom
=""

OR , you can use VBA


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("$A$2:$D$2")) Is Nothing Then Exit Sub
Application.EnableEvents = False
MsgBox "You may not edit this cell"
Application.Undo
Application.EnableEvents = True
End Sub
If you need a password to edit the cells, use an InputBox

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("$A$2:$D$2")) Is Nothing Then Exit Sub
Resp = InputBox("Enter Password")
If Resp <> "password" Then
Application.EnableEvents = False
MsgBox "You may not edit this cell"
Application.Undo
Application.EnableEvents = True
End If
End Sub

lenze

grohm
04-18-2007, 12:24 AM
Assuming your cells are already populated, you can use Data Validation to prevent them being changed
Custom
=""

OR , you can use VBA


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("$A$2:$D$2")) Is Nothing Then Exit Sub
Application.EnableEvents = False
MsgBox "You may not edit this cell"
Application.Undo
Application.EnableEvents = True
End Sub

lenze

thanks, lenze. that's just perfect. exactly what i was looking for. THX! :thumb

grohm
04-18-2007, 12:30 AM
hi lenze

it works well, but could you maybe explain some of the code to me? f.e. those particular ones:

"If Target.Count > 1 Then Exit Sub"

"Intersect" i was looking in help but somehow i didnt really get it....

"Application.EnableEvents = False"

thanks for your efforts.

have a nice day

greetz

ben

lenze
04-18-2007, 08:06 AM
"Target >1" simply applies the code only if one cell is changed. This would allow you to select more than one cell and ClearContents. You can remove this line if you want.

Intersect simpy limits the code to instances where the Target(ie cell changed) is in the defined Range("$A$2:$D$2")

Application.EnableEvents = False turns off future change events. In the posted code, when we use Application. Undo we would fire a second and thrid ...etc. change which might cause Excel to crash. It simply stops the code from running again after the Undo. After the Undo, we set it back to True

lenze

lenze
04-18-2007, 08:11 AM
"Removed by lenze"

grohm
04-18-2007, 08:21 AM
lenze, thanks for the explanation. now i understand. very nice.

have a good one.

greetz

ben

Simon Lloyd
04-18-2007, 09:37 AM
Hi you could also take a look at my kb entry which has annotation on the code, the entry does exactly what you need...it may help you understand further what is going on!
http://vbaexpress.com/kb/getarticle.php?kb_id=931&PHPSESSID=e2e7346d0938ef53cdf6d5858c86dc36

Regards,
Simon

grohm
04-19-2007, 04:03 AM
Hi you could also take a look at my kb entry which has annotation on the code, the entry does exactly what you need...it may help you understand further what is going on!
http://vbaexpress.com/kb/getarticle.php?kb_id=931&PHPSESSID=e2e7346d0938ef53cdf6d5858c86dc36

Regards,
Simon

i will certainly have a look! thanks, Simon!!