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

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

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

Andrew
What you say is a paradox. You say that if the sheet is protected, no
one can select a different value in the DV cell, and then you say they can
still change the value in the DV cell.
If the sheet is protected, the user cannot change the value in a DV cell
(in my 2002 version of Excel), so you don't need anything. If the sheet is
not protected, or that cell is Unlocked and the sheet is protected, then the
user CAN change the value in that cell. That's when you need the
Worksheet_SelectionChange macro to deny the user access to the list without
a password. Post back and clarify what it is that you want to do. HTH
Otto
"Andrew" wrote in message
...
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

yes it is protected from "key strokes" but not from the "Mouse clicking the
drop down menu and changing the value" therefore it is not actually protected.

I feel this may be Excel settings.

How about an exchange of emails and I can show you with screen shots I
really appreciate your help with this.

my email is (right spelling just copy and paste)

Andrew


"Otto Moehrbach" wrote:

Andrew
What you say is a paradox. You say that if the sheet is protected, no
one can select a different value in the DV cell, and then you say they can
still change the value in the DV cell.
If the sheet is protected, the user cannot change the value in a DV cell
(in my 2002 version of Excel), so you don't need anything. If the sheet is
not protected, or that cell is Unlocked and the sheet is protected, then the
user CAN change the value in that cell. That's when you need the
Worksheet_SelectionChange macro to deny the user access to the list without
a password. Post back and clarify what it is that you want to do. HTH
Otto
"Andrew" wrote in message
...
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

Assuming that you're using Excel 2000, if the list is short, you can
enter it in the Data Validation dialog box, in the Source box. Then
users won't be able to select a different value.
If the list refers to a worksheet range, users can select a different
value from the dropdown list.

This behaviour was changed in Excel 2002.

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






--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

Hi Debra

So what you are saying is a Data Validation Dialog Box will stop a users
changing the value wants its been selected? which is what am looking for.
Sadly I have a huge list.




"Debra Dalgleish" wrote:

Assuming that you're using Excel 2000, if the list is short, you can
enter it in the Data Validation dialog box, in the Source box. Then
users won't be able to select a different value.
If the list refers to a worksheet range, users can select a different
value from the dropdown list.

This behaviour was changed in Excel 2002.

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






--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

HAHA well I just tested the validation list on Excel 2002 and guess what you
are right it does protect validation lists. However it must be the version I
use in work. I will make sure they change copies and inform you of this
version they use

"Otto Moehrbach" wrote:

Andrew
What you say is a paradox. You say that if the sheet is protected, no
one can select a different value in the DV cell, and then you say they can
still change the value in the DV cell.
If the sheet is protected, the user cannot change the value in a DV cell
(in my 2002 version of Excel), so you don't need anything. If the sheet is
not protected, or that cell is Unlocked and the sheet is protected, then the
user CAN change the value in that cell. That's when you need the
Worksheet_SelectionChange macro to deny the user access to the list without
a password. Post back and clarify what it is that you want to do. HTH
Otto
"Andrew" wrote in message
...
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

Yes, if you enter the values as a delimited list, in the Data Validation
dialog box, users can't change the cell.
For a long list, you could enter the values on the worksheet, then in
the Data Validation dialog box, remove the check mark from In-cell dropdown.
Then the cell can't be changed when you protect the sheet.

Andrew wrote:

Hi Debra

So what you are saying is a Data Validation Dialog Box will stop a users
changing the value wants its been selected? which is what am looking for.
Sadly I have a huge list.




"Debra Dalgleish" wrote:


Assuming that you're using Excel 2000, if the list is short, you can
enter it in the Data Validation dialog box, in the Source box. Then
users won't be able to select a different value.
If the list refers to a worksheet range, users can select a different
value from the dropdown list.

This behaviour was changed in Excel 2002.

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





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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

thank you, we got there in the end, you were most helpful

"Debra Dalgleish" wrote:

Yes, if you enter the values as a delimited list, in the Data Validation
dialog box, users can't change the cell.
For a long list, you could enter the values on the worksheet, then in
the Data Validation dialog box, remove the check mark from In-cell dropdown.
Then the cell can't be changed when you protect the sheet.

Andrew wrote:

Hi Debra

So what you are saying is a Data Validation Dialog Box will stop a users
changing the value wants its been selected? which is what am looking for.
Sadly I have a huge list.




"Debra Dalgleish" wrote:


Assuming that you're using Excel 2000, if the list is short, you can
enter it in the Data Validation dialog box, in the Source box. Then
users won't be able to select a different value.
If the list refers to a worksheet range, users can select a different
value from the dropdown list.

This behaviour was changed in Excel 2002.

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





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html





--
Debra Dalgleish
Contextures
http://www.contextures.com/tiptech.html

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

http://www.contextures.com/xlDataVal08.html
states this:

"Data Validation on a Protected Sheet

In Excel 2000 and earlier versions, you can change the selection in a data
validation dropdown, if the list is from a range on the worksheet. If the
list is typed in the data validation dialog box, the selection can't be
changed.

In Excel 2002 and later versions, neither type of dropdown list can be
changed if the cell is locked and the sheet is protected.

This MSKB article has information on the previous behaviour:

XL97: Error When Using Validation Drop-Down List Box
http://support.microsoft.com/default.aspx?id=157484"

so this is the reason 2002 does it but not 2000 and below.

Many thanks

Andrew




"Otto Moehrbach" wrote:

Andrew
What you say is a paradox. You say that if the sheet is protected, no
one can select a different value in the DV cell, and then you say they can
still change the value in the DV cell.
If the sheet is protected, the user cannot change the value in a DV cell
(in my 2002 version of Excel), so you don't need anything. If the sheet is
not protected, or that cell is Unlocked and the sheet is protected, then the
user CAN change the value in that cell. That's when you need the
Worksheet_SelectionChange macro to deny the user access to the list without
a password. Post back and clarify what it is that you want to do. HTH
Otto
"Andrew" wrote in message
...
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

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

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