How to set a macro to input a password and not let the user know what the password is?

Hi all, here is my problem that i made into an example form:

I have a restaurant that has two excel files, one is the ORDER SHEET and one is the RESTAURANT DATABASE files. The order sheet gets filled up by the employees in the restaurant and that file calls up data from the RESTAURANT DATABASE files by using VLOOKUP. But the RESTAURANT DATABASE file is password protected and the employees dont know the password. The problem here is that when the employees fill up the order sheet the file will ask the password of RESTAURANT DATABASE. Is there a macro around this that when they're in the order sheet and there's a password prompt there will be a macro to override the password. So basically im asking for a way to make them able to input a password whenever they are prompt in the order sheet but not be able to know what the password is, is this possible?






Here is an example to help:
excel forum restaurant order sheet.xlsx
restaurant database.xlsx

The password for the database is abc123
I apologize since the VLOOKUPS on the order sheet will not work since the links are localized in my computer, If there was anyway i can make it downloadable and the vlookups accessible without editing it i would but i dont know how.

So in the order sheet, the employee types either HTDG, TCO, HMBRG, FRNCHF in the item code column and the columns beside it are the ones that are vlookup-ed to the database. But if you do that without opening the database file it would prompt for a password, so here is where i'd like a macro that can override the password so they can use the order sheet freely, but still be blocked from the database file. I hope someone can help in any way, thanks.

bump, anyone? :\

Kjy1989,

Just thinking at the moment but you could give each employee a standard, low level, password which if they enter and get right runs a macro to unlock the database, carries out some operation, then locks the database again with the same password. the problem may be having the database file open while they are working, it would be unprotected at that point in time.

Mowgli

That's really my dilemma, how to make the database file open enough for them to use, but not for them to copy or edit. Too bad i dont know how to do SQL or MsAccess so i have to do with excel. After weeks of searching i may think this is just wishful thinking.

Leith Ross, thank you very very much for your comprehensive post. I am digesting what you posted as i'm writing this reply and i will let you know if i have any problems/questions. Thank you and i appreciate the effort.

Hi Leith Ross, I opened your file as is, did not move the files anywhere in the computer and just opened it in the zip file. When i type a code in column B it returns an error saying Run time error '53' : File not found. I am trying my best to understand your very complex (for my standards at least) macro and cannot pinpoint the problem. What am i doing wrong?