How do I password protect a drop down list

pattigander

New Member
Joined
Jun 20, 2023
Messages
17
Office Version
  1. 365
Platform
  1. Windows
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
 

Excel Facts

Repeat Last Command
Pressing F4 adds dollar signs when editing a formula. When not editing, F4 repeats last command.
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?
 
Upvote 0
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.
 
Upvote 0
It's an excel form I created. The dropdown is from the data validation list function.
 
Upvote 0
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.
 
Upvote 0
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:
VBA 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
AB
1NAMEPWD
2A1
3B2
4C3
5D4
6E5
Sheet2
 
Upvote 0
Would I password protect the sheet with the name and passwords so that no one can see them and use them unauthorized?
 
Upvote 0
You can protect password protect the sheet with the name and passwords & also make it very hidden.
very hidden.jpg
 
Upvote 0

Forum statistics

Threads
1,224,755
Messages
6,180,766
Members
452,996
Latest member
nelsonsix66

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top