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!!
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.