How do I password protect a drop down list

[pattigander]

I am trying to password protect each item in a drop down list. I found on a forum VBA code but need to understand it so I can edit it and use it with different names in the dropdown and in different locations. Below is the code. If someone can explain how to modify it I would be grateful or if you are a consultant and can provide assistance for a fee.

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

 

[Akuini]

I am trying to password protect each item in a drop down list

What dropdown list? data validation?

in different locations.

it's in 1 cell or multiple cell?

Can you explain in more detail what you're trying to do?
How the users enter the name in the cell? by typing or can it be by copy-paste multiple cells at once?

 

[pattigander]

I am taking a system where measurements are recorded in a hard copy form to electronic so that I can do data analysis. the form will have a drop down for the name of the person entering the data of the measurement they took. For security I don't want someone to enter data under another person's name hence the drop-down list. When they select their name, it would ask for a password. The code I suppled works I just don't understand some of the parts so that I can edit it and make it work for my application.

 

[Akuini]

the form will have a drop down

What form? a userform?
and the dropdown is a combobox?

 

[pattigander]

It's an excel form I created. The dropdown is from the data validation list function.

 

[Akuini]

The code I suppled works I just don't understand some of the parts so that I can edit it and make it work for my application.

Which parts? what changes do you want to apply?

 

[pattigander]

I need to be able to change the location of where the names will be located as well as add to the list of names with passwords. I've played around with the code a bit and have moved the name input location on the sheet and changed it in the code and that worked. What I have not been able to do was change the names on the list and/or add more names to the list with the lookup on another sheet and have it work. I get an error where it highlights the code for this code line Private Sub Worksheet_Change(ByVal Target As Range) as to where the problem presumably resides.

 

[Akuini]

Ok, this is how I would do it:
Let’s say you put the name & password in Sheets("Sheet2").Range("A2:B5") & the cell to enter the name is in another sheet cell B7.
Try this code:

Option Explicit


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

If Target.Cells.CountLarge > 1 Then Exit Sub
If Not Intersect(Target, Range("B7")) Is Nothing Then
        Dim c As Range, i As Long, va
        va = Sheets("Sheet2").Range("A2:B5")  'name in col A & password in col B

        pwd = Application.InputBox("Password for " & Target & ":", "Enter Password", Type:=2)
        
        For i = 1 To UBound(va, 1)
            If Target = va(i, 1) Then
                If pwd <> va(i, 2) Then Oops = True: Exit For
            End If
        Next

            If Oops Then
                Application.EnableEvents = False
                        MsgBox "Bad password"
                        Target = ""
                Application.EnableEvents = True
            End If


End If

End Sub

Data in sheet2

Book1
	A	B
1	NAME	PWD
2	A	1
3	B	2
4	C	3
5	D	4
6	E	5
Sheet2

 

[pattigander]

Would I password protect the sheet with the name and passwords so that no one can see them and use them unauthorized?

 

[Akuini]

You can protect password protect the sheet with the name and passwords & also make it very hidden.