Password for Dropdown List?

Hello,

I'm required by my manager to produce password protected dropdown list (I use the data validation feature) for purchase order requester. I have been working on it for 2 days now, I somehow got it but it doesnt seem foolproof; as i often get error message after runnning it a few times.

Basically, all I need is to come up with a unique password for each name for listed in the "Requester". Whenever I click on it, it will prompt me to enter password.Different names requires different password, otherwise it will return as a blank cell.


I really appreciate if any of people here can help me with this, as I'm new to Excel programming. Also, kindly show me the steps how to edit data validation if possible.

Please advise,

Thank you very much.
Faiz

p/s:as attached is the requester list.

Not sure what you are asking for. Are you saying that when Mike choses his name from the dropdown list, you want Excel to ask for a password (unique to Mike)? What happens when the password is properly entered? What happens when it's not?

My bad for not explaining it in depth. Yes, only these people Bob, Mike, Alan and Pete are allowed to make the request, but they are all have their own password when they need to select their names from the list.

Say when password for Mike is correctly entered, Mike can use his name,but only his name. He cannot pick others name i.e when he tries to pick Bob from the list, different password will be needed which only Bob knows. Same goes to the others.

Whenever wrong or no password is entered, the dropdown list will return as a blank cell.


Thank you

Something like this put in the SHEET module to watch cell B7, edit the password constants at the top of the macro:

Option Explicit
Const Mike As String = "Mike1"
Const Alan As String = "Alan1"
Const Bob As String = "Bob1"
Const Pete As String = "Pete1"

Private Sub Worksheet_Change(ByVal Target As Range)
Dim cell As Range
Dim pwd As String
Dim Oops As Boolean

Application.EnableEvents = False

For Each cell In Target
    If Not Intersect(cell, Range("B7")) Is Nothing And cell <> "" Then
        pwd = Application.InputBox("Password for " & cell & ":", _
                    "Enter Password", Type:=2)
        Select Case cell.Value
            Case "Mike"
                If pwd <> Mike Then Oops = True
            Case "Bob"
                If pwd <> Bob Then Oops = True
            Case "Alan"
                If pwd <> Alan Then Oops = True
            Case "Pete"
                If pwd <> Pete Then Oops = True
        End Select
        
        If Oops Then
            MsgBox "Bad password"
            cell = ""
        End If
    End If
Next cell

Application.EnableEvents = True
End Sub

Originally Posted by phaiz_nova87

Hopefully I'll just be as good in the near future and could later offer help to other people.

Well, that's the goal ultimately. Welcome to the village.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.


In your new thread, be sure to attach your workbook so others can see and help debug the error you're receiving.