how to password protect a validation list?
Ohhhh I got ya, thanks very much
"Gord Dibben" wrote:
The reason I posted the code is because you wanted a user to be able to select
from the DV dropdown once then have further choices unavailable.
The code I posted does that.
Have you started with the DV cell unlocked?
Then protected the sheet with a password?
The user will be able to select one time from the DV lsit then it will become
locked.
Paste the code into the worksheet module as instructed.
Gord
On Tue, 19 Dec 2006 00:28:01 -0800, Andrew
wrote:
Hi Gord
I am not new to Excel but not advanced so bare with me pleae. This is what i
have found. In Excel 2002 when a work book is locked, a validation list
cannot be changed.
However, in Excel 2000 this does not work and a protected worksheet does not
protect Drop Down Validation Lists therefore the value can be changed at
will. However I can use the (IN-Cell Cropdown) which will hide the list once
it has been used therefore protecting it.
Your code is interesting and I tried it as an experiment on 2000 but no such
luck unless i did it wrong. Thank you for your help.
"Gord Dibben" wrote:
Andrew
You can first unlock the cell(s) with the DV list via
FormatCellsProtectionUncheck "Locked" then protect the sheet with a password.
When a user selects a value from the DV list, this event code will lock that
cell so further changes are not permitted.
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
Application.EnableEvents = False
If Not Intersect(Target, Range("A1")) Is Nothing Then
If Target.Value < "" Then
ActiveSheet.Unprotect Password:="justme"
Target.Locked = True
End If
End If
ActiveSheet.protect Password:="justme"
enditall:
Application.EnableEvents = True
End Sub
Change "justme" to whatever Pword you used.
If you have more than one DV cell you can expand the Target range....e.g.
A1:A10
As each of those gets used, they will become locked.
This is sheet event code.
Right-Click on the sheet tab and "View Code"
Copy/paste the above into that sheet module.
Gord Dibben MS Excel MVP
On Mon, 18 Dec 2006 12:09:00 -0800, Andrew
wrote:
Sorry it is hard to explain.
I can lock the spreadsheet which means if I type in any cell it wont let me
change the value, this will also stop me changing the value in a cell from a
validation list. However although this protects the spreadsheet from
keystrokes it does not stop any one can click on the arrow of the validation
list and change the value. Therefore bypassing the password protection
(accidently)
My question is can i put a password on the small arrow on the validation
list so that the list is password protected?
Or do you have a different method?
Andrew
"Otto Moehrbach" wrote:
Andrew
You have 2 questions that are not related. The first asks how to
prevent access to the DV cell and the second asks how to protect the cell
AFTER a selection has been made from the DV list. Did you mean to ask those
2 questions?
For the first, you can use a Worksheet_SelectionChange event macro. You
can write code into that macro to first negate the selection, then ask for a
password, then reselect that cell if the right password is given.
I don't know what you mean in your second question. What action by the
user do you want the user to not be able to do with that cell?
Be aware that Excel is not intended to be a secure platform, and it
isn't. Anyone with the right knowledge can break any security scheme you
come up with. HTH Otto
"Andrew" wrote in message
...
Hi
I would like to know how I could password protect a validation list.
In more detail what I would like is when I click on the cell with the drop
down menu a password is asked before the menu appears.
OR
Any other method to protect a cell once you have selected your information
from the drop down menu.
Andrew
Gord Dibben MS Excel MVP
|