+ Reply to Thread
Results 1 to 12 of 12

Password protect dropwn list and VLOOKUP attached values in subsequent rows

  1. #1
    Registered User
    Join Date
    03-09-2021
    Location
    england
    MS-Off Ver
    365
    Posts
    6

    Question 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!!!

  2. #2
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: Password protect dropwn list and VLOOKUP attached values in subsequent rows

    It would be easier to help and test possible solutions if you could attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file.
    You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
    Practice makes perfect. I'm very far from perfect so I'm still practising.

  3. #3
    Registered User
    Join Date
    03-09-2021
    Location
    england
    MS-Off Ver
    365
    Posts
    6

    Re: Password protect dropwn list and VLOOKUP attached values in subsequent rows

    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?
    Attached Files Attached Files

  4. #4
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: Password protect dropwn list and VLOOKUP attached values in subsequent rows

    Try the attached file. It has a sheet named "Passwords" which contains the country names and their corresponding passwords. The sheet is "VeryHidden" so users cannot see the passwords. You can add or delete countries and passwords as needed. You no longer need formulas in B2:D2 of Sheet2. Make a selection in A2.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    03-09-2021
    Location
    england
    MS-Off Ver
    365
    Posts
    6

    Talking Re: Password protect dropwn list and VLOOKUP attached values in subsequent rows

    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

  6. #6
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: Password protect dropwn list and VLOOKUP attached values in subsequent rows

    You are very welcome. Security in Excel is not that strong. Anyone who wants to break it can do so by doing a little research online. So how secure it is will depend on the skill level of the user and how how strong their desire to get access to the passwords. You can get some protection by going through the following steps:

    To protect your macros, you have to protect your VBA Project. Do the following:
    -hold down the ALT key and press the F11 key to open the Visual Basic Editor
    -click on 'Tools' on the top menu
    -click 'VBAProject Properties'
    -click the 'Protection' tab
    -click the box to the left of 'Lock project for viewing' to put a check mark in it
    -enter your password and then confirm it and click 'OK'
    -close the VB Editor
    -save your workbook as a macro-enabled file and close it
    When you re-open the file, you will not be able to see the macros unless you enter the password. Keep in mind that this type of protection is not very strong and anyone who really wants to get at your macros can probably do it with a little research. I hope this helps.

  7. #7
    Registered User
    Join Date
    03-09-2021
    Location
    england
    MS-Off Ver
    365
    Posts
    6

    Re: Password protect dropwn list and VLOOKUP attached values in subsequent rows

    Hi Mumps1,

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


  8. #8
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: Password protect dropwn list and VLOOKUP attached values in subsequent rows

    My pleasure.

  9. #9
    Registered User
    Join Date
    03-09-2021
    Location
    england
    MS-Off Ver
    365
    Posts
    6

    Re: Password protect dropwn list and VLOOKUP attached values in subsequent rows

    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!

  10. #10
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: Password protect dropwn list and VLOOKUP attached values in subsequent rows

    Try:
    Please Login or Register  to view this content.

  11. #11
    Registered User
    Join Date
    03-09-2021
    Location
    england
    MS-Off Ver
    365
    Posts
    6

    Re: Password protect dropwn list and VLOOKUP attached values in subsequent rows

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

  12. #12
    Forum Expert Mumps1's Avatar
    Join Date
    10-10-2012
    Location
    Toronto, Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,989

    Re: Password protect dropwn list and VLOOKUP attached values in subsequent rows

    You are very welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Pull the subsequent data for repeat values (Vlookup/Index - Macth)
    By pvkvimalan in forum Excel General
    Replies: 3
    Last Post: 12-26-2019, 06:14 AM
  2. [SOLVED] vlookup with random gaps in rows (hard to explain see attached sheet!)
    By gasdesign in forum Excel General
    Replies: 10
    Last Post: 11-07-2017, 10:56 AM
  3. [SOLVED] vlookup that returns first value then subsequent values.
    By Dan27 in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 01-13-2015, 07:36 AM
  4. Replies: 2
    Last Post: 11-17-2014, 04:28 PM
  5. Replies: 2
    Last Post: 01-17-2013, 08:39 AM
  6. [SOLVED] Password Controlled Cell Entry and Subsequent Actions
    By DrEcosse in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 06-10-2012, 02:53 PM
  7. Replies: 2
    Last Post: 07-16-2005, 11:05 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1