+ Reply to Thread
Results 1 to 15 of 15

Dropdown list with password defined from another excel file

  1. #1
    Registered User
    Join Date
    02-12-2021
    Location
    London
    MS-Off Ver
    2010
    Posts
    8

    Question Dropdown list with password defined from another excel file

    Hello everyone,
    I need your help with my application:
    • Three inspectors to inspect various parts. Each part has its own inspection record file (“Inspection record” workbook). Say I will have 500 of these inspection files.
    • When an inspector completes his inspection, he is supposed to sign his name on the inspection record file and export that to PDF (see “Inspection record” workbook – cell C6). Password match name can the name would display
    • Currently I have hardcoded in each inspector’s password in a private sub on Sheet 1 of “Inspection record” workbook.
    I’m not VBA programmer but I was able to search online a come up with a solution for my application, however it is half-way done. Say if one of current inspector quits his job, then I have to change all the hardcoded modules already built into my inspection record files.
    Question: I think there must be a way to store a list of inspectors and their password in a separate file/module (e.g. on a separate “Password” workbook) then all the 500 inspection record files can refer back to that inspector list for password and name verification.

    I would appreciate all your help. Thanks and have a good day.



    Additional info:

    C6 on "Inspection record" is a name but it requires a password to enter. Says Alan chooses his name from the dropdown list, then enter his password. if password is not correct, then there will be an error message box display and the C6 cell turns blank - i.e. name and password must match to turn C6 cell on. otherwise it is blank

    However, the password and list of names are "hard-coded" into the Sheet1 of "Inspection record" workbook. This is hard coded, so I want to have my "Inspection record" check "Password" workbook for list of names and corresponding passwords. so if a person quits, then I will not have to edit all the inspection files. Instead I will only need to edit "Password" workbook. Hope it makes senses.
    Attached Files Attached Files
    Last edited by nhimconde; 02-14-2021 at 01:44 PM. Reason: edit attachments

  2. #2
    Forum Moderator AliGW's Avatar
    Join Date
    08-10-2013
    Location
    Ipswich, England
    MS-Off Ver
    MS 365 Subscription Insider Beta Channel (Windows 11 64-bit)
    Posts
    60,497

    Re: Dropdown list with password defined from another excel file

    As this appears to be a VBA query, I am moving the thread to that section for you.
    Ali


    Enthusiastic self-taught user of MS Excel who's always learning!
    Don't forget to say "thank you" to anyone who has offered you help in your thread. You can reward them by clicking on * Add Reputation below theur user name on the left, if you wish.


    Forum Rules (updated September 2018): please read them here.
    How to use the Power Query code you've been given: help here. More about the Power suite here.

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

    Re: Dropdown list with password defined from another excel file

    It's not quite clear what the purpose of the password is. The drop down in C6 is a list of names not passwords. Where would you enter the password? Also, do you want to prevent the file being saved unless the correct password is entered? Please clarify in detail. You could have the names and corresponding passwords in a separate file but then the report file would have to open this file, search for the name and password and then close the file. This can be done. We would need the name including extension of the file and the full path to the folder where it is saved.
    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.

  4. #4
    Registered User
    Join Date
    02-12-2021
    Location
    London
    MS-Off Ver
    2010
    Posts
    8

    Question Re: Dropdown list with password defined from another excel file

    Hello Mumps1

    Attachment 718749

    On the "Inspection record" workbook, C6 is a name but it requires a password to enter. Says Alan chooses his name from the dropdown list, then enter his password. if password is not correct, then there will be an error message box display and the C6 cell turns blank - i.e. name and password must match to turn C6 cell on. otherwise it is blank

    However, the password and list of names are "hard-coded" into the Sheet1 of "Inspection record" workbook. This is hard coded, so I want to have my "Inspection record" check "Password" workbook for list of names and corresponding passwords. so if a person quits, then I will not have to edit all the inspection files. Instead I will only need to edit "Password" workbook.

    Yes, it would be great if the "Inspection record" workbook would not be saved to a PDF when the C6 cell is blank.

    Hope it makes senses.
    Last edited by nhimconde; 02-14-2021 at 01:46 PM.

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

    Re: Dropdown list with password defined from another excel file

    Your link doesn't work. See the yellow banner at the top of this page for instructions to attach a file.
    Says Alan chooses his name from the dropdown list, then enter his password
    After Alan chooses his name in C6, where would he enter his password? The password would have to be entered in a different cell so that the macro could compare the name to the password to see if the password matches the name. Also, I would need the name including extension of the file containing the names and passwords and the full path to the folder where it is saved.
    Last edited by Mumps1; 02-14-2021 at 01:53 PM.

  6. #6
    Registered User
    Join Date
    02-12-2021
    Location
    London
    MS-Off Ver
    2010
    Posts
    8

    Re: Dropdown list with password defined from another excel file

    The files are viewable to me. You can open "Inspection record" and click on C6 dropdown list, it will ask for a password.

    Steps as follow:
    1- Choose name (e.g. Alan)
    2- There will be a input box appears, Alan needs to enter his password (in this case, the password is hardcoded as "Alan1" in Sheet1 of Inspection Record workbook)
    3- if password is not correct, then there will be an error message box display and the C6 cell turns blank
    4 - If password is correct, then C6 will turn to display "Alan"

    The filepath of the file including names and password is: "C:\drive\Password.xlsx"

    Yes, it would be great if the "Inspection record" workbook would not be saved to a PDF when the C6 cell is blank.
    Attached Images Attached Images
    Attached Files Attached Files
    Eager to learn from you and share with others

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

    Re: Dropdown list with password defined from another excel file

    Try the attached files. Sheet1 in the Password workbook is "VeryHidden" so that the names and passwords are not visible. Save the Password file in the folder you mentioned and then close it before selecting a name in C6. If you ever have to change the names and/or passwords, you will have to unhide it in the Visual Basic Editor using the Properties window. After you make the changes, make the sheet "VeryHidden" again. Notice that the button is gone. The macro will save the file automatically if the password entered is correct.
    Attached Files Attached Files
    Last edited by Mumps1; 02-14-2021 at 02:54 PM.

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

    Re: Dropdown list with password defined from another excel file

    I updated the file in the previous post.

  9. #9
    Registered User
    Join Date
    02-12-2021
    Location
    London
    MS-Off Ver
    2010
    Posts
    8

    Re: Dropdown list with password defined from another excel file

    +1 Reputation for Mumps1

    However, now I don't see Sheet1 on the Password.xlsx file, how would I update the password list if the list changes later?
    In reality, I would password protect the file and only me can see the file. Other inspectors shall not able to see the password list.

    Thanks

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

    Re: Dropdown list with password defined from another excel file

    The macro has to open the Password file in order to check if the password matches the name. If you allow Sheet1 to be visible, then any inspector who completes his inspection will be able to see all the names and passwords when the macro opens the file. If you ever have to change the names and/or passwords, you will have to open the file, unhide Sheet1 in the Visual Basic Editor using the Properties window. After you make the changes, make Sheet1 "VeryHidden" again and re-save the file. I hope that makes sense.

  11. #11
    Registered User
    Join Date
    02-12-2021
    Location
    London
    MS-Off Ver
    2010
    Posts
    8

    Question Re: Dropdown list with password defined from another excel file

    To Mumps1 : Hi, somehow the PDFActivesheet (save to PDF) sub not working properly within your updated Inspection Record.

    In the Inspection record workbook, I want to have content of C7 and C8 prefilled into the name of PDF file to be created.
    It is not working right now. Mumps1, do you know why? Thank you

    Also, if password is correct, the Save file dialog appears, and if you click Cancel, consequent message still says "Report for [name] have been saved"

    Please help
    Attached Images Attached Images

  12. #12
    Registered User
    Join Date
    02-12-2021
    Location
    London
    MS-Off Ver
    2010
    Posts
    8

    Re: Dropdown list with password defined from another excel file

    I understand why the PDFActivesheet Sub is not working now

    In the PDFActivesheet, there is lines

    Please Login or Register  to view this content.
    With current version of Inspection record, the Password workbook is the active sheet, not the Inspection record - that's why C7 and C8 values are blank.

    Hope Mumps1 can help revise his codes in the "Inspection record" file to optimize the PDFActivesheet sub.

    Thanks

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

    Re: Dropdown list with password defined from another excel file

    Try the attached file. I have modified the PDFActivesheet macro.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    02-12-2021
    Location
    London
    MS-Off Ver
    2010
    Posts
    8

    Re: Dropdown list with password defined from another excel file

    Thank you Mumps1 - the new PDFActivesheet works like a charm now.

    I see that you hardcoded "Inspection Record.xlsx" into the sub PDFActiveSheet coding - and it works well if I have only one Inspection record file.

    One problem with that is I will have multiple Inspection Records files, says "Inspection records_01", "Inspection records_02", "Inspection records_03".... "Inspection records_5900"

    Is there a way to get the name of the currently opened "inspection records_[n]" into the where you currently hardcode file name "inspection records.xlsx" ?

    Mumps1, sorry for adding more requirements. I know I should have listed all the requirements straightforward from beginning, Thank you.

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

    Re: Dropdown list with password defined from another excel file

    Try the attached file. Please note that the name of the file is "Inspection record_1" not "Inspection records_1". Change the name in the code to suit your needs. Also, in order for the macro to work properly, only one "Inspection record_[n]" file can be open at a time.
    Attached Files Attached Files

+ 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. Password for Items in a Dropdown List
    By Ceceley in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 12-21-2020, 05:08 PM
  2. Select Name from Dropdown List and Password Validation
    By zulfaizan in forum Excel Programming / VBA / Macros
    Replies: 14
    Last Post: 12-04-2017, 12:48 AM
  3. Using a defined name within a dropdown list
    By Simps in forum Excel Formulas & Functions
    Replies: 4
    Last Post: 10-28-2015, 09:25 AM
  4. [SOLVED] Dropdown list with password for a whole column
    By pol04 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 07-29-2015, 07:53 PM
  5. Excel macro dropdown list password
    By aleks86 in forum Excel General
    Replies: 0
    Last Post: 12-10-2013, 05:48 AM
  6. Request password from a dropdown list selection
    By tantcu in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-01-2013, 04:27 AM
  7. [SOLVED] Password for Dropdown List?
    By phaiz_nova87 in forum Excel General
    Replies: 7
    Last Post: 08-29-2013, 01:25 PM

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