May 26, 2004

Keeping It Safe, Part 3

Here's yet another way to achieve a certain level of protection for your Workbooks. It comes in handy if you just want to help prevent accidental deletion of formulas or data without actually using the Protect Sheet (Protect Workbook) feature that I mentioned in my last post.

First, turn off Gridlines by selecting Tools, Options and then the View tab to untick the Gridlines checkbox.

Select a cell or range of cells, then use Copy (right click menu), and while pushing the Shift key, select the Edit menu and then Paste Picture.

You should end up with an 'invisible' shape like this.

Move it to your preferred location and drag the handles to change the size (Pushing the Alt key at the same time will help you align it with neighboring cells)

You're finished. You can also use the Camera button (You can find it by selecting Tools, Customize and then scrolling down the Tools menu of the Commands tab. Drag it across to any toolbar). Don't forget to change the Line Color to No Line.

One advantage of using the Camera is you can add text to the original cells from where you copied and it will appear on the 'invisible' shape. In fact any change you make will also change the 'invisible' shape automatically. One advantage of the Paste Picture method is that you can change Transparency by selecting the Fill Color you want, then right click to bring up the Format Picture dialog box, the select the Colors and Lines tab on the left side. Move the Transparency slider to the percentage you want (Transparency is only available for Excel XP and Excel 2003)

When using Protect Sheet (Protect Workbook), you find yourself unable to lock certain cells such as link cells for Combo Boxes etc. (Combo Boxes will not work if you lock the link cells) Using 'invisible' shapes you can shield these unlocked cells so that they cannot be selected with the mouse (you can still delete them by using the arrow keys so please be careful).

You might also think about making several invisible shapes and then Grouping them together(right click menu) to make an 'invisible template'. You'll find that you can still use AutoFilters if you postion your 'template' around them. Just move it out of the way when you want to edit the cells underneath and then repostion it while pushing the ALT key.

Comments: Post a Comment

This page is powered by Blogger. Isn't yours?