Hi, I did search in the forums and did not see this exact question. We send out confidential salary information as part of budgeting (in Excel). Locally, folks use their own passwords to protect the data. I need to consolidate the worksheets. Is there a VBA code I can use that will permit me to add a password that will ALWAYS unlock the file, even if others changed the password? Thought it would be worth a try to ask. Thanks!
If people are using their own passwords, there is nothing you can do in VBA to apply a master password that will override the user's password. If you implemented a password system that was built entirely in VBA you might be able to use a scheme like that, but once a user puts a password in using Excel protection, the game's over.
Note: This thread is about legitimate use and implementation of passwords and does not break any forum rules. However, do not post information to this thread that breaks the following rule:
7. Do not inquire about breaking the security of a protected workbook. (B, E)
B. Repeat offenders will get an infraction and banned for any subsequent offence.
E. Moderator should close the thread.
Last edited by 6StringJazzer; 01-02-2019 at 07:38 PM. Reason: added an omitted "not" in blue
The only way that I can think of implementing this is procedurally. You issue the workbook with the passwords in place and tell the users NOT to change the password. Then there is the vulnerability of having a user name / password list recorded somewhere.
One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
A picture is worth a thousand words, but a sample spreadsheet is more likely to be worked on.
Hello dmiller91,
You mention you need to consolidate the worksheets. Do you mean the worksheets that other people have in their workbooks? If so perhaps you could use VBA to transmit that data from the user's workbook to you.
Sincerely,
Leith Ross
Remember To Do the Following....
1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.2. Thank those who have helped you by clicking the Starbelow the post.
3. Please mark your post [SOLVED] if it has been answered satisfactorily.
Old Scottish Proverb...
Luathaid gu deanamh maille! (Rushing causes delays!)
Thanks. I'm not trying to break into worksheets or violate password rules. I'm responsible for the data and keep the main source file password protected in my care. I parse it and send it out to users, all with the same password, which we then tell them to change. However, I do like the idea of assigning the password before I send it out. In that case, only two of us would know the password, and either one loses it, the other will have it. In addition, the Finance person that reviews it will have the password. That's more secure than it is now, all sent out with the same password.
A team member had asked me if there was a way to super protect the worksheet where it can always be unlocked by the right person, and it sounds like no. I guess I can see why that would not be permitted, but I would imagine there are others in my position, that have to maintain some level of control, yet still have access to the data.
I do need to suck the data back out upon completion, I have used VBA only to the extent of reading and modifying other's codes, so I'm not sure I could figure out a VBA code that would allow me to suck data out of a password protected file. Even if they export it to a file they send to me, it should be password protected.
The other thing I had thought about was using Share Point and user permissions to have them upload the data, but it's too late in the game to try that now. If that's something I could do, I might look at it for next year.
Thanks all!
As for password assignments, you might be able to come out with a "hash" that encrypts combination of the user id and date on the file. This means you will not have to remember and record the passwords and can issue new passwords every day. They may be weird like 63d^fi92@ which will also assure that the person who receives it will write it down. You can have mercy on your end users an maybe use only the first 10 characters of the generated password.
So instead of recording everyone's password, you can re-generate it as needed. Keep this program to yourself
Do a web search on encrypting algorithms, pick one and don't let anyone else know what method you use or what combination of user id and file date you are using.
Embedding VB code to encrypt the file and password protecting the VB Project will compromise this system to an extent, but if you wish to take that risk, then the password can be applied when the user opens the file. This is a double-edged sword. Only the user can open that file, but if the user is on vacation, the alternate cannot open the file.
Of course, as mentioned above, if the end user changes the password, then all bets are off.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks