Password promt in drop down list to unprotect a protected sheet

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

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

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim sPassWord As String
    Const PW As String = "aaa" 'Change to suit
    
    If Target.Address = "$D$7" Then
        If Target.Value = "Engineering" Then
            sPassWord = Application.InputBox("Enter Password to unprotect worksheet.", "Worksheet Protected")
            If sPassWord = PW Then
                Me.Unprotect PW
            Else
                Range("$D$7").ClearContents
                MsgBox "Incorrect password entered."
                Me.Protect PW
            End If
        Else
            Me.Protect PW
        End If
    End If
End Sub

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

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

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

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!!

Glad to help - it's how I learn!