Password promt in drop down list to unprotect a protected sheet

[Kavv]

Hello Excel forum,

I am relatively new to VBA and I am struggling with the following:

I have a sheet that is protected with a password.
I have a drop down list with io cell D7 with 2 selections: "Sales" or "Engineering".

Basically, when Sales use the sheet, they will only use it in the protected mode with "Sales" already selected in the drop down menu. I would like to be able to select "Engineering" from the the drop down list which should then prompt me to input the password to unprotect the entire sheet and unhide the cells that are hidden. If the incorrect password is input, a box saying "incorrect password" should appear and hte sheet should remain protected as it already is.

Note: Selecting "Engineering" from the drop down list when the sheet is not protected already unhides certain cells that only Engineering can use (hence why the sheet is protected for Sales as we do not want them to use the engineering cells)

Thank you very much for your help with this

[dangelor]

Try adding this to the worksheet's code module...

Please Login or Register  to view this content.

[Kavv]

Hi dangelor,

Thanks for the reply. I've tried the code however it doesn't seem to work.
I assume this is because, since the sheet is already protected, i cannot actually select "Engineering" in the drop down list in D7, therefore theTarget.Value will never be "engineering"??

When i try to change the drop down list to "Engineering" i get the standard pop saying the "the cell you are trying to change is on a protected sheet [...] You might be requested to enter a password"

Attachment 642815

[dangelor]

Unprotect the sheet manually, then change that cell's format protection to unlocked before re-protecting the sheet.

[Kavv]

So this nearly works!

The issue now is that once i select "engineering", it will unhide the cells that are hidden at the same time that the password promt comes up.
Ideally, once Engineering is selected, only the password promt comes up and:

1. if the password is correct, the sheet unprotects and these cells become visible
2. if the password is incorrect, D7 reverts back to "Sales" leaving the hidden cells hidden

[mikerickson]

I think this will do what you want. It uses Excel's password unprotect dialog box (with hidden characters) rather than an input box.

Please Login or Register  to view this content.

[Kavv]

Right.

Thank you both. with both the codes and a bit of logical thinking and conditional formatting i was able to do what I needed. I modified the codes a little as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim sPassWord As String
Const PW As String = "BFC"

If Target.Address = "$D$7" Then
If Target.Value = "Sales" Then
Range("D8").Value = " "
Me.Protect PW
Else
If Target.Value = "Engineering" Then
sPassWord = Application.InputBox("Enter Password to unprotect worksheet.", "Worksheet Protected")
If sPassWord = PW Then
Me.Unprotect PW
Range("D8").Value = 1
Else
Target.Value = "Sales"
MsgBox "Incorrect password entered."
Range("D8").Value = " "
Me.Protect PW

End If
Else
Me.Protect PW
End If
End If
End If
End Sub



I effectively included a cell that i could then reference in the conditioanl formating to make my hidden cells only visible once hte passwordwas entered and the cell value changes


Thanks again!!

[dangelor]

Glad to help - it's how I learn!