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

 

[pattigander]

I have sheet1 with cell b7 as the cell for the name being taken from sheet2 using the data validation list function. I put in the code and it doesn't ask for a password. I am clearly doing something wrong...

 

[Akuini]

Maybe somehow Application.EnableEvents got turn off?
Try running this code first:

Sub try()
      Application.EnableEvents = True
End Sub

 

[pattigander]

I am not having any luck with this... nt recommendations? I appreciate all your help...
Patti

 

[Akuini]

Could you upload a sample workbook (without sensitive data) to a sharing site like dropbox.com or google drive?
And then share the link here.

 

[pattigander]

Here is the link to the sheet. I highlighted a cell that I would want to have a drop-down list of names and a password assigned.... Not drop down or password created so anything added is fine for the test application. I appreciate your help... please let me know if you are able to access the link. If not, I'd be happy to email you the form...

Groove Sheet Working Doc.xlsx

Shared with Dropbox

www.dropbox.com

 

[Akuini]

Try this:

pattigander - Groove Sheet Working Doc

MediaFire is a simple to use free service that lets you put all your photos, documents, music, and video in a single place so you can access them anywhere and share them everywhere.

www.mediafire.com

 

[pattigander]

I am unable to download the file.

 

[Fluff]

Please do not put your email address in your posts, as spam bots regularly patrol sites like this looking for email addresses.

 

[pattigander]

Please do not put your email address in your posts, as spam bots regularly patrol sites like this looking for email addresses.

Thank you for letting me know.

 

[Akuini]

I am unable to download the file.

Not sure the link is broken, try this one:

pattigander - Groove Sheet Working Doc1.xlsm

Shared with Dropbox

www.dropbox.com