Protect original data from changes

[hutch@edge.net]

How's it going, Gurus? I'm always amazed at how quick and effective this forum is for answering my thousands of Excel questions. Thanks to all

I have a tracking log, stored on a network drive, that tracks open purchase orders on about 13 different buyers. Each buyer has access to this data, and would be interested in looking at it in different ways.

I have an idea for protecting my original data in this workbook, but allowing others to view and edit the data anyway they want. I thought I'd run it past the pros first, to see if they knew a better way, before I started trying to code it.

My idea was to put a macro in the workbook open event that opens an input box, "Enter password or select okay." If they enter the correct password, the macro exits, and they are in the workbook. Any changes made after entering the correct password will be made to the original data.

If they enter anything OTHER than the correct password, the code would save the workbook AS another name, so now any changes made would be only made to a COPY of the original data.

This way, when I do my daily updates, I can open the file using the password, and save the file after editing. Anybody else would only get to edit a copy of my original.

The other question is..., is it possible to put a macro in the workbook exit event that requires a password to overwrite the original? Otherwise, the user could edit the data in the "saved as" version, then just overwrite the original file, either accidentally or maliciously. I know they could still overwrite it by editing the macro, but I don't think they're that savvy.

So, what do you think? Is this worth the effort, or is there already a better way out there to do what I'm trying to do?

Thanks in advance for your anticipated response.

[Paul]

Hi Hutch,

Rather than using worksheet protection and 'Save As' vs. 'Save', etc., it might be simpler to set a password to modify the workbook.

1. Make any changes you want to the workbook
2. Click File -> Save As (or in 2007, click the Orb -> Save As)
3. In the 'Save As' window, leave the name the same, and click the Tools dropdown button
4. Select 'General Options', then type in a 'Password to modify'. Click OK.

The next time you open the workbook you'll be prompted to type in the password to modify, or to click the Read Only button. At that point the user cannot overwrite the original file using Save or Save As.

[hutch@edge.net]

Thanks, Pjoaquin.

Your method is definitely MUCH simpler, and probably offers about the same level of protection as my suggestion. My worry is, it's easy to get around this one even with NO knowledge of VBA. The easy get around is that you can open read only, make any edits you want, and save as antoher filename. Then you simply save as again, and you can select the original filename and overwrite it. This not only saves the edited version as the original, but now the password protection is gone as well.

My thought was that even after saving the file as a different filename using my method, the exit event(?) was still in the workbook, preventing you from saving it as the specified filename, (original filename), without entering the password. As I've thought about it more, however, what would prevent a user from opening ANY other file, selecting save as, and overwriting my original data file? Then I've lost everything.

I've incorporated your suggestion, as that is definitely better than nothing, but am still interested in finding a more robust way to protect my original data.

Any other ideas?

[randell.graybill]

Protect the worksheet by locking all cells so they can't so much as select a cell.

Press the little square between the column letter and row number thus selecting all cells on the spreadsheet, next go to format cells format and click on the protection tab. Make sure the box next to locked has a black check mark in it (a grey check mark means only some cells are locked). Then press ok. Next go to tools protection and select protect sheet and select all options you wish to be able to do while the worksheet is locked (you can make it so no cells may even be selected). Then type a password into the textbox that allows the sheet to be unprotected later on(so you have full access to the sheet)press ok after you have you have selected what you want the sheet to be able to do while locked.

Go back to tools protection and this time choose lock workbook and set a password here too. All you have to do to modify your data is go back to tool, protection and select unlock worksheet and workbook. while the workbook is locked it prevents someone from saving the file, even if they try to do a save as it responds by giving an error message.

[Paul]

Best way to make a file read-only would be for the owner/administrator of the folder to make the file read-only, or to give users that have access to the folder Read/Execute permissions, but not Write/Delete/etc.

You can keep your own editable copy, or give yourself read/write/del/exec rights to the file/folder level.

Perhaps implement some folder permissions in conjunction with my original 'read-only' password suggestion?

[hutch@edge.net]

You both make good points, and I'll probably use a little of both. I overlooked the simplest and most obvious way to protect my original, and it didn't dawn on me until I just read Joaquin's post. I'll just update the copy on my personal drive everyday, and copy it to the network. I'll still want to use the read only command, though.

Thanks, guys. This thread is now officially solved.