After an Excel course I was running at the highly impressive Sage HQ in Newcastle, one of the delegates pointed out that it’s all very well protecting your spreadsheets, but how do you stop users cutting and pasting between unlocked data cells? The effect of this is to potentially mess up the references to the data cells affected even in the protected cells. Of course it would be possible to write VB code to prevent this, or to use form fields or some other method of data entry, but is there a simpler way?
In Excel XP and later, the protection capabilities have been substantially enhanced and allow choice as to what a user can and cannot do. In addition, there is the ability to ‘Allow users to edit ranges’. I wondered whether this might be the solution, so tried leaving all the cells in a sheet locked, but then adding the data cells to an ‘allowed range’ – in this case without setting a password. This did seem to work. When the sheet was protected the protection did not allow cutting and pasting in any locked cells (which was of course all the cells – including those in the allowed range), but because of the allowed range, with no password, data could be entered in these cells. I don’t know whether this is a well-known feature and obvious method of implementing protection since XP changed the protection options, or an unintended but useful consequence. I also haven’t yet tested the way it works thoroughly and am waiting to hear how well the solution works for the person who asked the question in the first place.