|If you don't understand my point, then perhaps you don't understand the code you posted above. |
If you don't understand the code you posted above, then perhaps you should not be running it in your workbook.
If you don't know exactly what it is doing, how do you know that it's not doing something harmful to your machines?
I'll break it down for you, although all of this information can be gleaned from the Excel Help files.
Locked Cells and Protected Workbooks
1 - Locking cells has no effect unless the workbook is Protected.
2 - If you Unlock all the cells in your workbook, users can enter data even if the workbook is Protected.
3 - You can not Lock or Unlock cells with the workbook Protected. It must be Unprotected to change this setting.
Result: Cells must be Locked and the workbook must be Protected in order to prevent users from entering or changing data in the Locked cells.
- The Before_Save code posted above Unprotects the workbook, Locks the cells with data in them and then Protects the workbook again.
Result: Cells with data in them cannot be altered once the code Locks them and Protects the workbook.
Limitations of Shared Workbooks
- As per the Help files, the ability to Protect and Unprotect shared workbooks does not exist. The workbook must be unshared before the Protection can be enabled or disabled.
Result: When the Before_Save code posted above tries to Unprotect the shared workbook, it fails because neither the user nor any VBA code is allowed to Protect or Unprotect a shared workbook.
Putting this all together, the bottom line is this:
If you want to prevent data entry you have to both Lock the cell and Protect the workbook. Since you can't Lock a cell while the workbook is Protected, you have to Unprotect the workbook first. The problem is that you can not Unprotect a workbook while it is set to Shared; therefore you have to remove the Sharing first.
This can all be done via code, but it is going to cause problems if other users have the workbook open at that time because of what happens if you try to Unshare a workbook that multiple users are editing.