Best Way to Password a Microsoft Workbook that is to be Shared with Others

[FRED_SHEET]

I have a Microsoft Excel Workbook with four worksheets. One of these Worksheets (Data Entry Worksheet) has a Macro for clearing data entry cells on this one worksheet. I would like to provide this Workbook to others to use. I would like to keep them from changing any portion of the workbook – rows, columns, formulas, Macros, formatting, etc. The only thing I want them to do is enter data into the data entry cells (which have been unprotected) on the Data Entry Worksheet with the Macro.

They can then print this worksheet and/or save it. They can then use the Macro to clear the data from the data entry cells and start over again if they wish. They will also be able to print out the other worksheets – one which contain instructions for using the workbook, another with reference information, and a blank worksheet for manually recording data onto a printed worksheet for entering on to the worksheet with a Macro. All the cells in the worksheets have been locked except for the data entry cells.

My question is how do I best prevent changes to this Workbook?

Do I password protect each worksheet? Do I password protect the Macro?

When I click on file it gives me five options (1) Mark as Final (2) Encrypt with Password (3) Protect Current Sheet (4) Protect Workbook Structure and (5) Add Digital Signature.

I do not want to have the user enter a password.

Considering what I have outlined what is the SIMPLEST way of doing this? Also, if I have to use more than one password can I use the same password?

I am new to password protection.

Thanks for your help.

 

[Johndinho]

Hi Fred,

I would add a password to the whole sheet and make sure no changes can be made.
I would set a WorksheetOpen event to display a userfom and use this for the user to enter the data.
On clicking OK; VBA can then enter the data to the relevant parts of your worksheet.
The userform can also have the ability to print particular data from the sheet(s) based on parameters set by the you/the user.

Personally I would use the protect sheet option from the Review tab - yes you can use the same Password for each sheet. The vba will need the password in the code to allow updates to the data but this will be done automatically so the user will not see/enter the password. VBA can also lock the sheets again afterwards.
Yes you can protect the code as well (Same password if you want) But Excel is not that secure - if someone with a little knowledge of vba does a google search; they can quickly find code to remove your password. Excel passwords are a good deterrent but not foolproof.

Hope this points you in the direction of an answer. There is a reasonable amount of work here (I don't know your VBA level) but it will be a very good project to learn from

 

[FRED_SHEET]

Hi Fred,

I would add a password to the whole sheet and make sure no changes can be made.
I would set a WorksheetOpen event to display a userfom and use this for the user to enter the data.
On clicking OK; VBA can then enter the data to the relevant parts of your worksheet.
The userform can also have the ability to print particular data from the sheet(s) based on parameters set by the you/the user.

Personally I would use the protect sheet option from the Review tab - yes you can use the same Password for each sheet. The vba will need the password in the code to allow updates to the data but this will be done automatically so the user will not see/enter the password. VBA can also lock the sheets again afterwards.
Yes you can protect the code as well (Same password if you want) But Excel is not that secure - if someone with a little knowledge of vba does a google search; they can quickly find code to remove your password. Excel passwords are a good deterrent but not foolproof.

Hope this points you in the direction of an answer. There is a reasonable amount of work here (I don't know your VBA level) but it will be a very good project to learn from

Thanks, I will check out the use of a User Form. I am not familiar with these forms so it will be a learning experience. As to my VBA level I am a very beginner. In fact the Macro I wrote for this worksheet is the first one I have attempted. One of the drawbacks I envision for the form is the number of data entry cells, it could be up to 60 cells.

 

[Johndinho]

60 cells is a lot of information but if users enter that much now; it won't be any more work for them.

Userforms are scary looking but quite straight forward when you get used to them

Good Luck!!