How to save and restore cell's locked property

[gbrenkman]

I have a worksheet that has some locked and unlocked cell, and the worksheet is protected with a password that only I know.

When the user closes/saves the document, I want to prompt if they want to save the file as an "Internal Use Only" file that preserves the status of the locked/unlocked cells. Of course, a normal save will do that.

But I also want to prompt at close/save if they want to save the file as an "External Use Only" file that will be sent outside the company to customers. In that case, I want to set ALL the cells to locked status before the save, so that customers won't be able to change any of the cell values (e.g., quoted prices).

When the file is opened, I want to prompt for a password that only the internal users will know. If the password is entered, code needs to restore the previous locked/unlocked status of the cells so that internal users can again work with the file.

I'm thinking I'd have to loop through all the cells from A1 to LastCell, and perhaps store cell addresses and settings onto a hidden worksheet. Then use those values to reset the locked/unlocked status of the cells if the password is entered upon open. But I'm not sure how to do that, or if there's a better way.

Why not save two versions of file, one with some cells locked/unlocked and others all locked? Well, I suppose that's an option, but if the "internal use" file gets accidentally deleted, then there's no way to recover and have a file where some cells are locked and others unlocked for internal use.

Any ideas/code ideas, folks? Thanks!

 

[FDibbins]

If the file is PW protected, they should not be able to save the file anyway?

 

[gbrenkman]

I want users to be able to save the file.

The worksheet and workbook are protected with a master password only I know.

When I save the file and distribute it to internal users, some cells are locked and others are unlocked. That allows internal users to make changes in the cells I want them to be able to change, but not other cells.

When the internal users go to save the file, I'll put up a user form asking if they want to save an internal or external version. If they pick internal, it just saves the file as is with the mix of locked/unlocked cells. If they pick external, then VBA would set all the cells to locked so that the external users won't be able to change anything.

Upon open, VBA code will show a user form and prompt for a "unlock" code that only internal users will know. If the internal user enters the magic code, then I need VBA to return the cells from ALL locked back to the previous combination of locked/unlocked cells so the internal users again can edit where I want them to but not in cells that I don't want them to change.