May 27, 2004


None other than Debra Dalgleish, MVP of Contextures was kind enough to point out some confusion regarding a previous post "Now You See it, Now You Don't" on May 13.

The point of the post was to illustrate how an area of a Worksheet can be used to have formulas in the cells themselves, with a clear AutoShape above to make a message appear using Conditional Formatting.

The post was confusing because I used a date related formula in a cell beneath and another date related formula in the Conditional Formatting. Let me say that there is no connection between the two formulas. What is more important is that the clear AutoShape to display the message at the right time, and the cell formula to be hidden using regular formatting (white Font Color)and Conditional Formatting (changing to black Font Color). The text on the clear AutoShape itself is white.

When the Conditional Formatting is activated, the cell font color (and cell background) will turn black to make the white text in the clear AutoShape appear like this.

Still confused? Send me an email at the Contact Me! link on the bottom left side this site and I'll do my best to explain.

Please feel free to contact me whenever you like. I'll try to help with any Excel related inquires (or direct you to people who can) and like I mentioned, feedback is always welcome!

Thanks again Debra!

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.

May 25, 2004

Keeping It Safe, Part 2

Excel makes it possible to protect your files in a variety of ways. Apart from the methods I showed in my last tip, one of the best ways is Worksheet Protection (Workbook Protection is something else you can consider depending on your particular requirements). Select Tools, Protection and then Protect Sheet.

Depending on your version of Excel, there is quite a difference in the options that are available but the basics have not changed. First of all, allowing users (including yourself) to select locked or unlocked cells is something you must decide. Unlocked cells can be edited even when the Worksheet is protected, locked cells cannot. To unlock or lock cells, drag across your selection and right click to select the Format Cells dialog box. The far right Protection tab is what you want, tick or untick the Locked checkbox option accordingly.

Obviously, the safest protection option is not to allow any user to select anything and this can be a good thing depending on your situation. (Don't forget you can password protect sheets and workbooks too). However, sometimes you may want to keep some cells unlocked so that users can input or modify data so these cells should be unlocked before you protect the sheet.

Unfortunately Protection can also cause problems. In some versions of Excel, Protection does not allow you to use AutoFilters (you can enable filters with Excel XP or Excel 2003 scrolling down to the Use Autofilter option, but this feature won't work for anyone else on a network with earlier versions)

Debra Dalgleish, Excel MVP, has a good solution to this problem with earlier versions on her site Contextures (some very good tips to be found there) Here's a link to her tip on AutoFilter - Protected Sheet. Copy the VBA code and place it in your Workbook as per her instructions (Don't forget to change your Worksheet name accordingly)

Okay, that's it for now. Next time I'll have a very simple protection tip that may come in handy for "partial protection".

May 23, 2004

Keeping It Safe, Part 1

You put a lot of work into your Workbooks. Are they as safe as they should be?

Save As...
When you save your work, there are some very good safety options available to you. Select Tools on the Save As dialog box then General Options.

Always create backup?
The choice is yours. Creating backup files will use up more disk space, but hopefully that should not be a problem. I always make a point to select this option, it's better to be safe than sorry. You might also consider saving under different filenames if you think that more than one backup file may be prudent or even create backups of your backup files.

Two options, a Password to open the file and a Password to modify it. I wonder just how many people use the first option (and what kind of environment they must work in) but the second option is quite handy, it also offers an read only option for anyone who does not intend modifying the file. Just keep in mind that anyone can change something and then save under a different name or location. The file can also be deleted very easily. Use these options with caution.

Read-only recommended
A good safety option. This is what appears when you open the Workbook.

You can also add the Toggle Read Only button to a toolbar so this option can be selected after the workbook has already been opened. Open the Customize dialog box from the top Tools Menu, then the Commands tab. Scroll down the File menu until you see the button and drag it across to a toolbar.

It also indicates whether the file is in read only mode or not. Very useful if you are unsure of the file's status.

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