Is there a way to make a user enter a password when they enter a cell? Say I
have a cell that uses data validation to get a list of states. I would like
to make it so that whenever a user clicks or moves to that cell that they
have to enter a password before they can change or select a state.

Thanks

Dwaine

There are two methods you could use, Dwaine. I think Method 1 works best,
but you can be the judge.

Method 1:
-----------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address = Range("A10").Address Then
pword = InputBox("Enter Password To Edit State:")
If pword = "Dwaine" Then ActiveCell.Select Else ActiveCell.Offset(1,
0).Select
End If
End Sub

Open your Excel workbook, switch to the VB Editor, select the Sheet object
that contains the cell you want to protect, and paste the above 6 lines of
code into the main work area window. Change the reference to cell €œA10€� in
line 2 to the address of the cell you want to protect and change €œDwaine€� in
the 4th line of code to what ever you want for a password. Save the
workbook; no other action is necessary.

Note that if the worksheet is to be protected for other reasons, the
"Locked" checkbox for the cell's Protection format must be cleared before
protecting the worksheet (this is counter-intuitive, but results in leaner
code and efficient execution (as opposed to adding more code to respond to
the sheet protection).

--------------------


Method 2:
--------------------
Alternatively, if you have Excel2003, try this:

Ensure that the Protection format of the single cell is set to "Locked" (use
Format, Cells, Protection, and check the Locked checkbox). Then, choose
"Tools, Protection, Allow Users to Edit Ranges..." Follow the instructions
there, adding a 'New' range that points to the single cell. Enter a password
for the cell. Don't monkey with the "Permissions..." button unless you are
in a networked environment with shared files.

This should work fine, too. The only problem is that once the cell is
unlocked with the password, it stays unlocked until the file is closed and
reopened.


Good luck and let me know if you have any trouble with this.

Jay




"Dwaine Horton" wrote:

Is there a way to make a user enter a password when they enter a cell? Say I
have a cell that uses data validation to get a list of states. I would like
to make it so that whenever a user clicks or moves to that cell that they
have to enter a password before they can change or select a state.

Thanks

Dwaine

Method two is the way I would like to go. I have done the steps but it is
not asking for a password. When I protect the sheet it doesn't even let me
go into that field. I do have Excel 2003. I believe method 1 will work but
I can't use any macros in this version as that will limit who can use the
form.

"Jay" wrote:

There are two methods you could use, Dwaine. I think Method 1 works best,
but you can be the judge.

Method 1:
-----------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address = Range("A10").Address Then
pword = InputBox("Enter Password To Edit State:")
If pword = "Dwaine" Then ActiveCell.Select Else ActiveCell.Offset(1,
0).Select
End If
End Sub

Open your Excel workbook, switch to the VB Editor, select the Sheet object
that contains the cell you want to protect, and paste the above 6 lines of
code into the main work area window. Change the reference to cell €œA10€� in
line 2 to the address of the cell you want to protect and change €œDwaine€� in
the 4th line of code to what ever you want for a password. Save the
workbook; no other action is necessary.

Note that if the worksheet is to be protected for other reasons, the
"Locked" checkbox for the cell's Protection format must be cleared before
protecting the worksheet (this is counter-intuitive, but results in leaner
code and efficient execution (as opposed to adding more code to respond to
the sheet protection).

--------------------


Method 2:
--------------------
Alternatively, if you have Excel2003, try this:

Ensure that the Protection format of the single cell is set to "Locked" (use
Format, Cells, Protection, and check the Locked checkbox). Then, choose
"Tools, Protection, Allow Users to Edit Ranges..." Follow the instructions
there, adding a 'New' range that points to the single cell. Enter a password
for the cell. Don't monkey with the "Permissions..." button unless you are
in a networked environment with shared files.

This should work fine, too. The only problem is that once the cell is
unlocked with the password, it stays unlocked until the file is closed and
reopened.


Good luck and let me know if you have any trouble with this.

Jay

Hi Dwaine -

After you follow the steps for Method 2, you need to protect the sheet. I
neglected to clarify that earlier, but I think this will make it work.

You are offered a button to "Protect Sheet..." at the bottom of the "Allow
Users to Edit Ranges..." dialog box (where you assign a password to the
cell). Alternatively, you can just use the standard "Tools, Protection,
Protect Sheet..." after you've assigned the password to the cell.

Jay


"Dwaine Horton" wrote:

Method two is the way I would like to go. I have done the steps but it is
not asking for a password. When I protect the sheet it doesn't even let me
go into that field. I do have Excel 2003. I believe method 1 will work but
I can't use any macros in this version as that will limit who can use the
form.

"Jay" wrote:

There are two methods you could use, Dwaine. I think Method 1 works best,
but you can be the judge.

Method 1:
-----------------
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If ActiveCell.Address = Range("A10").Address Then
pword = InputBox("Enter Password To Edit State:")
If pword = "Dwaine" Then ActiveCell.Select Else ActiveCell.Offset(1,
0).Select
End If
End Sub

Open your Excel workbook, switch to the VB Editor, select the Sheet object
that contains the cell you want to protect, and paste the above 6 lines of
code into the main work area window. Change the reference to cell €œA10€� in
line 2 to the address of the cell you want to protect and change €œDwaine€� in
the 4th line of code to what ever you want for a password. Save the
workbook; no other action is necessary.

Note that if the worksheet is to be protected for other reasons, the
"Locked" checkbox for the cell's Protection format must be cleared before
protecting the worksheet (this is counter-intuitive, but results in leaner
code and efficient execution (as opposed to adding more code to respond to
the sheet protection).

--------------------


Method 2:
--------------------
Alternatively, if you have Excel2003, try this:

Ensure that the Protection format of the single cell is set to "Locked" (use
Format, Cells, Protection, and check the Locked checkbox). Then, choose
"Tools, Protection, Allow Users to Edit Ranges..." Follow the instructions
there, adding a 'New' range that points to the single cell. Enter a password
for the cell. Don't monkey with the "Permissions..." button unless you are
in a networked environment with shared files.

This should work fine, too. The only problem is that once the cell is
unlocked with the password, it stays unlocked until the file is closed and
reopened.


Good luck and let me know if you have any trouble with this.

Jay

The problem is I want to lock some cells and unlock some cells. If I lock
this cell and uncheck the select locked cells then I can't select it, if I
unlock the cell then I can select it but it doesn't ask for the password to
change. I want the users to only select and tab to unlocked cells.

I tried reversing my locked and unlocked but when I go to the protection
part I can either have select locked cells and select unlocked cells checked
or just select unlocked cells checked. It will not let me just check the
select locked cells.

Users will be using tab to go from cell to cell.

"Jay" wrote:

Hi Dwaine -

After you follow the steps for Method 2, you need to protect the sheet. I
neglected to clarify that earlier, but I think this will make it work.

You are offered a button to "Protect Sheet..." at the bottom of the "Allow
Users to Edit Ranges..." dialog box (where you assign a password to the
cell). Alternatively, you can just use the standard "Tools, Protection,
Protect Sheet..." after you've assigned the password to the cell.

Jay

You have a conundrum... You can't achieve all of your objectives in my
opinion; there is some mutual exclusivity among your objectives which a

1. Protect the worksheet so that users can tab only among unlocked cells.

2. Unlock and password-protect a single cell so users can tab to it as per
objective (1).

3. Avoid the use of macros.

I have never found a solution to this through any mechanisms that are
"built-in" to Excel (through Excel 2003). You are out of luck unless a
future release of Excel provides some relief for this common issue.

I believe that your only choice is to implement the original macro that I
posted earlier (Method 1). It meets all of your objectives except objective
3. Let me know what is constraining you from implementing macros and maybe
we can work around those constraints. Otherwise, I think we have exhausted
all options.

Jay







"Dwaine Horton" wrote:

The problem is I want to lock some cells and unlock some cells. If I lock
this cell and uncheck the select locked cells then I can't select it, if I
unlock the cell then I can select it but it doesn't ask for the password to
change. I want the users to only select and tab to unlocked cells.

I tried reversing my locked and unlocked but when I go to the protection
part I can either have select locked cells and select unlocked cells checked
or just select unlocked cells checked. It will not let me just check the
select locked cells.

Users will be using tab to go from cell to cell.

"Jay" wrote:

Hi Dwaine -

After you follow the steps for Method 2, you need to protect the sheet. I
neglected to clarify that earlier, but I think this will make it work.

You are offered a button to "Protect Sheet..." at the bottom of the "Allow
Users to Edit Ranges..." dialog box (where you assign a password to the
cell). Alternatively, you can just use the standard "Tools, Protection,
Protect Sheet..." after you've assigned the password to the cell.

Jay

The purpose for not using macros is we have some users that may be filling
these out on handhelds. I was informed that the version of Excel can't
handle macros. I will talk to a few people and see what they want to do with
this info. Macro may have to be the way to go.

Thanks for your help.

Dwaine

"Jay" wrote:

You have a conundrum... You can't achieve all of your objectives in my
opinion; there is some mutual exclusivity among your objectives which a

1. Protect the worksheet so that users can tab only among unlocked cells.

2. Unlock and password-protect a single cell so users can tab to it as per
objective (1).

3. Avoid the use of macros.

I have never found a solution to this through any mechanisms that are
"built-in" to Excel (through Excel 2003). You are out of luck unless a
future release of Excel provides some relief for this common issue.

I believe that your only choice is to implement the original macro that I
posted earlier (Method 1). It meets all of your objectives except objective
3. Let me know what is constraining you from implementing macros and maybe
we can work around those constraints. Otherwise, I think we have exhausted
all options.

Jay