View Full Version : [SOLVED:] Protect Individual Cell with Formula?
golf4
08-24-2004, 09:20 PM
Hi, everyone -
I just had a quick query. Working on my income calculation tool, I ran into something I haven't tried before. First, my data entry sheet is fully protected, via password, except for cells I use to enter specific data for calculation.
What I'm wondering is what the best method is to accomplish the following (example): if cell A1 equals either 4 or 5, then how would I protect cell A2? I'm thinking it's got to be something like
=IF(OR(A1=4,A1=5),PROTECT(A2),"")? :dunno
Or could the same thing be accomplished in Conditional Formatting? Any help would be great.
Thanks,
Golf
Jacob Hilderbrand
08-24-2004, 10:04 PM
You can get tricky with validation, but the cell won't really be protected. What you need is VBA. Put this code in the sheet code section for the sheet you want it to work on. Change "Sheet1" to the actual sheet name, you can also use ActiveSheet if you prefer.
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("A1").Value = 4 Or Range("A1").Value = 5 Then
Sheets("Sheet1").Unprotect Password:="Password"
Range("A2").Locked = True
Sheets("Sheet1").Protect Password:="Password"
Else
Sheets("Sheet1").Unprotect Password:="Password"
Range("A2").Locked = False
Sheets("Sheet1").Protect Password:="Password"
End If
End Sub
golf4
08-24-2004, 11:15 PM
Hi, Jacob -
Thanks for the response. I really appreciate your suggestion.
Take care,
Golf
XL-Dennis
08-24-2004, 11:29 PM
http://www.mrexcel.com/board2/viewtopic.php?p=500582#500582
In general I would prefer that OP at least let the boards know that the same question has been asked on several boards so people who respond are aware of it.
Kind regards,
Dennis
Jacob Hilderbrand
08-24-2004, 11:34 PM
Hi, Jacob -
Thanks for the response. I really appreciate your suggestion.
Take care,
Golf
You're Welcome :)
Take Care
Zack Barresse
08-25-2004, 10:05 AM
Hi Golf,
Why not just skip the VBA and use Data Validation? You can use Custom, formula ...
=((A1<>4)+(A1<>5))<>1
Jacob Hilderbrand
08-25-2004, 07:17 PM
That's what I meant by
You can get tricky with validation, but the cell won't really be protected While the validation will stop you from typing into the cell, it unfortunately won't stop you from clearing the cell contents or from copy/paste.
Zack Barresse
08-25-2004, 07:36 PM
Good point Jake. I was just thinking along the lines that simple may be better. If not, it's just another option for Frank. :yes
golf4
08-25-2004, 08:02 PM
Thanks, again, Jake and Zack -
KISS ( ya gotta love it!!!). I really appreciate all of the ideas. I had also posted my query on www.mrexcel.com (http://www.mrexcel.com) (thread posted below) where I kind of expounded a little more of my actual situation. I figure I could always use my exposure to VBA, so I decided to go that way. Plus I figure I could always "cannibalize" the code I have now for other things. I've used Validation in so many other places in the tool as it is that Staff is getting real used to just "entering" through the warning message boxes without reading them.
http://www.mrexcel.com/board2/viewtopic.php?t=102946&highlight=
Dennis - Thanks for the suggestion.
Anyway, I really appreciate all the help from everyone. :wavey:
Take care,
Frank
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.