I work very hard to build bullet-proof applications that are as locked-down as possible from user meddling and just plain honest things that they MIGHT do in the course of their use of the application that might mess things up. I have been able to control nearly everything I need to do so I can sleep nights with Excel apps in “The Wilds” of my clients’ networks (Workbook Protection, Worksheet Protection, Project Protection). I also create my own Custom Menus or Ribbons to focus the user on the use of just the spreadsheet in front of them without any extra unnecessary menu or Ribbon items. But there is one major hole in my armour that I cannot control and that is becoming more and more a problem for me.
Despite unlocking specific cells and then protecting a Worksheet with a password, a user who copies and pastes from a cell in a source Workbook into a cell in a target Workbook will find their unlocked cells become formatted with the source cell’s formatting – even though the protection of the Worksheet specifically says not to allow the user to change formats in the protected worksheet – so the user can’t even FIX the copied format themselves.
Even better than that, if you copy from another instance of Excel (which does happen when you have customized menus and Ribbons) even the locked status of the target cell gets changed if the source cell is locked. This is a BIG problem for me in several applications I have in place.
Here is an example:
Figure 1 is the target Worksheet. It is formatted, Cells B1 and B2 are unlocked and Worksheet protection is ON.
Figure 2 is the source Worksheet (in another Instance of Excel) with cell B1 formatted but NOT unlocked.
Figure 3 is the result of a Copy Paste from Figure 2 to Figure 1. Notice that the formatting came along with the Paste, and although I can’t show it here, the target cell (B1) is now locked – so not only is the formatting messed up but it cannot be changed unless you remove the Protection (which kinda defeats the purpose methinks).
While this may seem like an obscure problem to many, I don’t think it is. In actual fact copying and pasting from spreadsheet to spreadsheet has always been common. It’s just that if you want to build a “bullet-proof” application where the user is protected from his or herself, then this is a major hole in the security of the file and my ability to assure the integrity of my application.
IMHO this is a major weakness and even in Excel 2010 I do not see this fixed. Sucks.