Password protect dropwn list and VLOOKUP attached values in subsequent rows

excelforum.com/excel-general/744273-password-for-dropdown-list

Linking on from the above thread, I have to come up with a password protected drop down list. I have created a dropdown list of authorities (cell: A3) and in the subsequent rows (B3, C3, D3) I have included a VLOOKUP function to find different data (e.g. age).

I managed to use the above macro and it worked! The only thing is when Excel asks the user for a password for A3, it shows the data included in B3, C3 and D3. It is only once the user has not been able to provide the correct password that the data then appears as: N/A, N/A, N/A

My question is: How do I password protect all cells based on the dropdown list in A3?

Any help would be greatly appreciated!!!

Hi there,

Attached is a rough example of what I am dealing with. I have created a dropdown list for countries/cities
(in this case France, Germany and London) in cell A2. I have then added a VLOOKUP function in cells B2, c2 and d2 in order
to then find "total number of participants", "percentage of males" and "percentage of females".

I have used a macro code from this excel forum in order to password protect each country/city. So for example, France
should be able to only see and access France's data (when they have been given the password).

Option Explicit
Const London As String = "London1"
Const France As String = "France1"
Const Germany As String = "Germany1"
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("A2")) Is Nothing And Cell <> "" Then
pwd = Application.InputBox("Password for " & Cell & ":", _
"Enter Password", Type:=2)
Select Case Cell.Value
Case "London"
If pwd <> London Then Oops = True
Case "France"
If pwd <> France Then Oops = True
Case "Germany"
If pwd <> Germany Then Oops = True
End Select

If Oops Then
MsgBox "Bad password"
Cell = ""
End If
End If
Next Cell


Application.EnableEvents = True
End Sub


However, the above code does not hide the numbers in B2, C2 and D2 when I try and type in a password. So the data can be seen!

How can I alter this code so that this data is ONLY seen if the user has a password?

Thank you so much. That was incredibly helpful! It now works like a charm.

My subsequent question to this is how I could make sure no one else can access those passwords.

I.e., how secure is the veryhidden sheet and is there a way of making it more secure?

Thank you again

Hi Mumps1,

Again, so helpful! That's all done though I understand it is not perfectly secure.

Hi again,

Sorry to bother you, but I am now struggling to add to this. I want the individual when they click on France for example to be able to access figures for B2:D3. In other words, I would like the code to generate figures for two rows. I have input this code but it is not working and returning this message "object variable or with block not set". The code:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Address(0, 0) <> "A2" Then Exit Sub
Dim pwd As String, fnd As Range, country As Range
pwd = Application.InputBox("Password for " & Cell & ":", "Enter Password")
If pwd = "" Then Exit Sub
Set fnd = Sheets("Passwords").Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
If Not fnd Is Nothing Then
If pwd <> fnd.Offset(, 1) Then
MsgBox ("Invalid password.")
Exit Sub
Else
Set country = Sheets("Sheet1").Range("A:A").Find(Target.Value, LookIn:=xlValues, lookat:=xlWhole)
Range("B2:D3").Value = country.Offset(, 1).Resize(2, 3).Value
End If
End If
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Target.CountLarge > 1 Then Exit Sub
If Target.Address(0, 0) <> "A2" Then Exit Sub
Range("B2:D3").ClearContents
End Sub


Please let me know what you think? Any help would be greatly appreciated!

You have been so incredibly helpful thank you!!!! It works!