Security - deny access to workbooks when employees leave company

[Lewej23]

I created a workbook that is used at my company called "Calculator". It basically simplifies the calculations needed in figuring out discounts for products. This workbook contains confidential information, so we want to prevent a current employee from taking it with them if they ever leave the company. I've done some research and here are my thoughts on each:

1. Encrypt with Password: user will still have the password when they leave & will still be able to open it.

2. Use VBA to not allow the workbook to open after a certain date: the calculator will be used for each customer's purchase and saved to a file (i.e. "Joe Schmoe's Discounts 2011", "Joe Schmoe's Discounts 2012", etc.). So if the workbook is programed to not open after a certain date, we would not be able to access the customer's past discounts workbooks.

I'm wondering if it is possible to have each employee set up an account with a User ID and Password, which I would keep in a seperate spreadsheet on a shared drive... when they open their workbook, they would need to put in their User ID and Password, the workbook would try to access the shared drive to check the password...
--- if the shared drive is accessable: it will verify the password and will let the user in (or won't if the password was incorrect)
--- if the shared drive is not accessable: it will start a 10 day countdown to when the entire workbook would expire unless it's able to access the shared drive and verify the password.

Again, I don't know if that's even possible... so I'm open to some suggestions here!

Thank-you!!!

[martindwilson]

you'll never prevent them from opening it if they really want to!

[exit151]

Possibly out of the scope of what you want, but the only way I can quickly think of to do something like that would be to consider moving your file (converting it) to something intra/inter-net based and run it through a web browser. As the previous poster indicated, a user with a file in hand and a little determination is going to get in. Even with a date expiration (if even possible?) by rolling back the clock.. If by storage on a non-local drive (shared file/folder) by simply clicking save as... or even copy/paste directly.. just my $.02 fwiw.

[TMS]

Ditto Martin: with basic Excel/VBA, a bit of determination and a password cracker should get you in without too much effort.

When people sent me password protected spreadsheets, I just to consider it a personal challenge ... admittedly, I was the systems manager. More often than not, I could get past the passwords. There are lots of things you can to stop the "average" Excel user ... but if it's commercially sensitive, it's not secure.

Maybe you could store the "database" on a network drive and give the users a "front end" spreadsheet which only accesses the data on an as required basis. One set of info at a time and clear it out on close.

Maybe ... ?