You know you’ve been using MS Office too long when….

  • Drafting a report by hand you glance at the paper expecting to see red and green squiggly underlines;
  • Whenever you forget how to program the video you look at the TV screen for a small dog wearing a cape;
  • You try to find the ‘Croissant Warming Wizard’ on your microwave;
  • Whenever you think of a good idea, a yellow light bulb appears by your left ear;
  • Your solution to a ‘bag full’ light on your hoover is to turn if off and then back on again;
  • You talk gibberish and expect the words to be corrected automatically as they leave your mouth;
  • You expect members of your family to have optional appearances and personalities;
  • You don’t wonder why you want to connect your toaster to the Internet;
  • You blame everything – faulty traffic lights, unhelpful shop assistants, running out of petrol – on Microsoft;
  • You put together a half hour long PowerPoint presentation to tell the children where you’re going on holiday next year.

Excel protection

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.

Excel protection