cell protection / Worksheet Protection
Posted by Rob Margolis on October 16, 2000 2:37 PM
I am having a problem with employees taking a protected worsheet and/or protected cells and simply copying the worksheet to a new name. This method seems to be bypassing my so called protected cells. I want employees, however, to be able to save the file name under a new name but only being able to edit certain cells. Under no conditions should they be able to copy protected cells and insert them elsewhere.
Posted by JWorley on October 17, 2000 10:40 AM
I am having somewhat of thesame problem and can't seem to get a direct answer, I have a simple monthly accounting workbook and need to protect cells w/formula's so the formula isn't overwritten by data entry, therefore blowing away the formula, I have tried protecting the entire workbook and locking the cells containing the formula's but anyone can just type any value into the cell and poof... If I find an answer I will let you know could you please do the same
Posted by Ben O. on October 17, 2000 2:11 PM
Rob,
Try password protecting both the worksheet and the workbook. If the workbook is protected, they won't be able to access the Move/Copy sheet command. They will be able to save it as a new name, but it will remain protected.
They'll still be able to copy the cells and paste them to another workbook, unfortunately. There might be a VBA command to disable the copy function, but I don't know it. However, if you're concerned about people using your formulas in another workbook, you can put those formulas on a another sheet and hide that sheet. With the workbook protected, they won't be able to unhide it. The cells on your main sheet can just reference the cells on your hidden sheet that contain the real formulas.
-Ben
Posted by Ben O. on October 17, 2000 2:18 PM
J,
There's a difference between protecting a workbook and protecting a worksheet. I once thought that protecting a workbook would protect the locked cells on every worksheet, but that's not what it does. You have to protect the workSHEET individually to protect its locked cells.
If a worksheet is protected, you will not be able to change locked cells on that sheet, and those formulas will be safe.
-Ben
Posted by Michele on October 19, 2000 11:14 AM
Same problem! How do you stop someone from copying a sheet when send it to them locked. When they copy and paste the protection is rendered useless.
Posted by Ben O. on October 19, 2000 2:18 PM
If the workbook is locked they won't be able to copy the sheet through the Edit > Move or Copy Sheet method. They will be able to copy and paste the protected cells, but you can make it so they can't copy the functionality of those cells by having those cells contain references to cells in a hidden worksheet rather than formulas. See my response to Rob for more detials about this.
Another thing you could do is change the scroll area of your worksheet so that protected cells are outside of the scroll area. This is a little dangerous though, since the users might not be able to see those cells if they can't scroll. But if they're outside of the scroll area, your users will not be able to select them at all. You can modify the scroll area by going to your VBA editor, selecting a worksheet, and entering the range that you want users to be able to scroll to in the properties window.
-Ben
