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.

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

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.

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.

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.

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.

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.

I updated the file in the previous post.

+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

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.

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

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

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

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.

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.