PDA

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