Microsoft

Office Q&A: How to disable Excel's error-checking and insert tabs in a Word document

Susan Harkins helps a few readers find simple solutions to common Office tasks, including how to print multiple modules and insert tabs in a Word document.

February was a busy mail month, but the solutions were relatively simple. I helped readers disable Excel's error-checking, print multiple Excel modules, and insert tabs after every character in a Word document. None of these solutions requires a downloadable demo file. Let's take a look at each one in detail.

Disable error-checking in Excel

Rocky doesn't like the green triangles that Excel displays when he enters a formula that Excel thinks might be in error. For instance, an inconsistent formula will trigger this feature. In Figure A, D5 contains a simple addition formula, while the cells above and below contain a MAX() function. Excel sees this as a possible error -- and in most cases, it would be. For this reason, Excel's error-checking is a helpful feature that I recommend leaving enabled.

Figure A

Figure A

Excel identifies possible errors.

This feature offers several options, which you can display by clicking the accompanying option button. The options will be relevant to the error. In the case of an inconsistent formula ( Figure B), you can copy the formula from above with a simple click -- or you can ignore the error. There are several options, but you'll usually fix or ignore the error.

Figure B

Figure B

Excel options for an inconsistent formula.

Occasionally, this feature will be annoying and, like Rocky, you'll want to turn it off. Doing so is easy enough:

  1. Click the File tab and choose Options. In Excel 2003, choose Options from the Tools menu.
  2. In the left pane, choose Formulas. In Excel 2003, click the Error Checking tab.
  3. At this point, you have two options: Uncheck the Enable background error checking in the Error Checking section to disable the feature or disable a specific rule by unchecking that rule in the Error checking rules section.
  4. Make your choice(s) and close the dialog box to return to your sheet.

Following this example, you could turn the feature off completely, but unchecking the Formulas inconsistent with other formulas in the region option ( Figure C) and leaving the rest of the error-checking feature enabled is probably a better choice.

Figure C

Figure C

You can uncheck a rule in the Error Checking Rules section.

Error-checking is an application setting, not a workbook setting. This means that disabling the feature or a rule will remain in effect for existing and new workbooks until you reset it. If this is important enough, you can add VBA statements to specific workbooks to exert specific control using VBA's ErrorCheckingOptions property ( Figure D). Specifically, you could capture the settings when opening a workbook and set them the way you want them for that specific workbook. Then, use the workbook's Close event to reset the feature's options, leaving them the way you found them when you opened the workbook.

Figure D

Figure D

Use VBA's ErrorCheckingOptions.

Bulk printing for VBA procedures

John has lots of VBA procedures in Excel and wants to print them all for documentation. Printing individual modules is tedious , because there are so many of them. Fortunately for John, the solution is simple. To print all of the procedures in a workbook, open the Visual Basic Editor (VBE) and choose Print from the File menu. Printing the Current Module is the default setting (Figure E). Change that to the Current Project to print all of the modules at once.

Figure E

Figure E

Choose Current Project to print all modules.

Insert a tab after every character

Roger wants to insert a tab after every character in a document. Fortunately, this isn't as hard as it sounds, thanks to Word's Find and Replace feature and the use of wildcards ( Figure F).

You'll need an example document with a few words if you want to follow along. Use any document you like and do the following:

  1. On the Home tab, click Replace in the Editing group -- or press [Ctrl]+[H].
  2. In the Find what control, enter ? - -that's a literal question mark.
  3. Enter ^&^t in the Replace with control.
  4. Click the More button if necessary.
  5. Check the Use wildcards option (Figure F).
    Figure F
    Figure F
  6. Click Replace All and then OK. Figure G shows the results.
    Figure G
    Figure G

If you skip step 5, this trick won't work, and that's where most users go wrong -- they forget to enable wildcards. The ? wildcard represents a single character. By using it alone, Word will find every character. The Replace With code has two components. First, ^& replaces the found character with itself. The second component, ^t denotes a tab. The combination replaces every character with the same character and a tab.

The only catch is the size of the default tab. This feature will insert the default tab. If it isn't suitable, you'll have to change that first. You can't actually control the size of the inserted tab using this technique. To learn how to change the default tab, read " How to change a Word document's default tabs."

Send me your question about Office

I answer readers' questions when I can, but there's no guarantee. When contacting me, be as specific as possible: For instance, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results?" might. I'm not reimbursed by TechRepublic for my time or expertise, nor do I ask for a fee from readers. You can contact me at susansalesharkins@gmail.com.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

0 comments

Editor's Picks