Missing Office Templates

Users of Microsoft Office that have upgraded to the 2013 version panic when when they click on File==>New and no longer see their customized templates. All they see is a dialog box recommending that they search online for templates.


Assuming that you did not actually delete the template files, the templates still exist – Office just needs to be told where the template files are stored.

From one of the Office programs (Word for example) choose File==>Options and then click on Save from the left-hand navigation bar. This will display the following:


The important setting is the Default personal templates location box. As you can see above, this box is blank. You have to enter the path to your customized templates as shown below. (If you are not sure where your templates are stored, do a search for *.dotx [Word] or *.xltx [Excel].) Click OK to exit the Options dialog box.


The next time that you click on File==>New you should see two choices (as shown below) Featured and Personal. Clicking on Personal should display all of your customized templates. (You may have to exit and re-start the particular Office program.)


Note that you have to perform these steps in each Office program for which you have created customized templates.


View VBA Consultants profile on LinkedIn

Copyright 2015 – VBA Consultants Ltd

Help With Words In Word

Every Microsoft Word user is familiar with Word’s squiggly red line used to highlight misspellings and how it will suggest correct spellings if you right-click the misspelled word. What many users are not aware of is that Word will assist you to determine whether you are using the correct word or should be using a synonym.

To check whether you are using a word properly according to its definition, right-click the word and choose Look Up, and then Encarta Dictionary from the short-cut menu. [Encarta Dictionary is used for North American English.]


This will open the Research Pane with a list of definitions. You can then read through the definitions to see if you are correctly using the word in question. At the bottom of the list are some hyperlinks with other reference sources.


If like you find that like you are always like using the same word like in a report you might want to like look for similar words that like have the same meaning, like you know synonyms. Right-click the word and choose synonyms from the short-cut menu. Unlike the previous example, you can either select one of the choices that Word presents, or choose to open the full Thesaurus in the Research Pane.


Word will also attempt to translate a word into certain non-English languages by choosing Translate from the right-click short-cut menu. Unfortunately, Latin is not one of the choices.


You can also access the Research Pane by clicking on the icon in the Review tab of Word’s ribbon.


Users of Word 2003 can access the Research Pane by selecting Tools==>Research from the menu bar.


View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd

Shortcuts for Lines in Word

Microsoft Word provides six shortcuts for placing specific styled lines across a page. Not only do these lines look better than (for example) holding the equal sign to repeat a double line across the page, but the length of these lines automatically adjust as the page’s left and right margins are changed.

To create these lines, move the cursor to a blank line in a Word document, type a specific character three times, and then press the enter key. The following screen shot shows both the special character to type and the resulting line inserted by Word.


This tip works in Word 2003, 2007, 2010, and 2013.


View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd

Word Navigation Pane

When reviewing a Microsoft Word document with many pages you may find yourself constantly scrolling up/down or using the Page Up/Page Down keys. To aid in navigating large documents Microsoft added the Navigation Pane starting with Word 2010.

The Navigation Pane is only available when a document contains Heading styles. At a minimum a document must utilize the Heading 1 style. The Navigation Pane will also recognize and use the higher heading styles (2,3,…).

To use the Navigation Pane you must ‘turn it on’ by checking the Navigation Pane check box in the Show section of the View tab on the ribbon.

[Read more…]

Word Keyboard Navigation

One of the ironies of using computer software programs with a graphical interface is that you have to move at least one hand from the keyboard to the mouse in order accomplish some command or task. When using a word processing program most users immediately reach for their mouse to move the insertion point to a new location, or to look at another portion of the document. This is very inefficient considering that you will be moving your hand back to the keyboard to continue typing once you have moved to the new location.

This article explains some of the methods to navigate a document with the keyboard when using Microsoft Word. These keyboard shortcuts should work in Word 2003, 2007, 2010, and 2013. NOTE: The ‘+’ between the key names indicates that you have to press the keys at the same time.

Keystroke(s) Movement
Left or Right Arrow One character left or right
Up or Down Arrow One line up or down
Ctrl+(Left or Right) Arrow One word left or right
Ctrl+(Up or Down) Arrow One paragraph up or down
Home or End Beginning or end of line
Ctrl+(Home or End) Beginning or end of document
Page Up or Page Down One screen up or down
Ctrl+Alt+(Page Up or Page Down) Top or bottom of the window
Ctrl+(Page Up or Page Down) Previous or next instance of browse object

Even remembering half of these navigation tips will make you more efficient at moving around a Word document.

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd

Word Keyboard Formatting

A prior post explained how to select text within a Microsoft Word document using the keyboard rather than the mouse. Once you have selected text, what do you do with it? The odds are that you either copy it and paste it somewhere else, delete it, or format it. If you routinely perform the same formatting option(s) on text, you can improve your efficiency with Word by learning the keyboard short-cut(s) for those options.

The following table lists the methods that can be used to format a document with the keyboard when using Microsoft Word. These keyboard shortcuts should work in Word 2003, 2007, 2010, and 2013. NOTE: The ‘+’ between the key names indicates that you have to press the keys at the same time.

Format Type Short-cut Keys
Align left CTRL+L
Align right CTRL+R
Bulleted list CTRL+SHIFT+L
Center CTRL+E
Copy format CTRL+SHIFT+C
Decrease font size CTRL+SHIFT+<
Decrease font size one point CTRL+[
Font name CTRL+SHIFT+F
Hang paragraph CTRL+T
Heading level 1 ALT+CTRL+1
Heading level 2 ALT+CTRL+2
Heading level 3 ALT+CTRL+3
Increase font size CTRL+SHIFT+>
Increase font size one point CTRL+]
Indent paragraph CTRL+M
Italic CTRL+I
Justify paragraph CTRL+J
Line space (1 line) CTRL+1
Line space (1.5 lines) CTRL+5
Line space (2 lines) CTRL+2
Normal style CTRL+SHIFT+N
Open font dialog box CTRL+D
Paste format CTRL+SHIFT+V
Reset character formatting CTRL+SPACEBAR
Reset paragraph formatting CTRL+Q
Small caps CTRL+SHIFT+K
Subscript CTRL+=
Superscript CTRL+SHIFT+=
Symbol font CTRL+SHIFT+Q
Underline continuous CTRL+U
Underline double CTRL+SHIFT+D
Underline word CTRL+SHIFT+W
Un-indent paragraph CTRL+SHIFT+M

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd


Corporate users of Microsoft Excel utilize large spreadsheets encompassing many rows and columns. Regardless of whether the spreadsheet is being used for budgeting, cash projections, depreciation, or acquisition analysis, there are usually a few cells containing some critical piece of information. Most users constantly scroll left and right, or up and down, to see the effect of changes on these critical cells.

Excel contains a feature known as the Watch Window. The Watch Window is a floating dialog box that displays the value of your ‘critical cells’ even when those cells are not currently displayed. Using this feature will not only save time, but also carpal tunnel syndrome.

To display the Watch Window in Excel 2007, 2010, or 2013 click on the Watch Window icon contained on the Formulas ribbon. [For Excel 2003 select Tools → Formula Auditing → Show Watch Window on the menu bar.]


This will display the Watch Window.


Click Add Watch on the menu bar. This displays a pop-up allowing you to either type a cell (or range) address or by clicking the icon, select the cell (or range) with your mouse.


When the correct cell (or range) is shown in the pop-up, click the Add button.


The Watch Window will then display the book name, sheet name, optional range name, cell (or range) address, current value of the cell, and the formula contained in the cell.


You can continue to add more additional items to the Watch Window by clicking on Add Watch on the menu bar.

The Watch Window will remain ‘in your face’ as you move around the spreadsheet. However, you also have the option of docking the Watch Window below the ribbon by dragging the window up towards the ribbon until it snaps into place.


You can turn off the display of the Watch Window by either clicking the Watch Window icon on the Formula ribbon or by clicking the ‘x’ in the upper right corner of the Watch Window.

Copyright 2014 – VBA Consultants Ltd

Automatically Shade Excel Rows

Before laser printers became common place (yes there was life before laser printers), people working with computer generated reports were used to green-bar paper. While the paper comes in various sizes, the most common is 14 7/8 x 11. The paper is in a carton of five thousand continuous sheets. The left and right sides of the paper have sprocket holes that align with the sprockets on an impact printer. The sprockets actually pull the paper past the print head as opposed to a laser jet’s method of feeding the paper in to the print mechanism. If you pay a little extra you can get the paper with perforations by the sprocket holes so that they can be cleanly removed from the report.

It was called green-bar paper because it had alternating sections of light green coloring imprinted on it. Depending on the point size of the report, each section might contain from three to five rows of print. These alternating colored sections allow the reader of the report to ‘line things up’ as the reader’s eyes travel across the report.

The rise of the laser jet heralded in the demise of the impact printers and with it green-bar paper. However, people still need to read landscape reports with compressed print that might squeeze the same amount of information onto a smaller page. If that is your situation, this post will allow you to mimic green-bar paper using two Excel functions along with the Conditional Formatting command.

ROW Function
The ROW function returns the row number of the current cursor location. As seen in the following screen shot, entering =ROW() in cell E2 returns 2, the row of the cursor when the formula was entered. This function by itself is not such a big deal. You could tell that the formula is in row 2 just by moving your eyes a few inches to the left. This function is the most useful when nested inside another function, or in some VBA code.


MOD Function
The MOD function returns the modulus of one number being divided by another – in plain English, the Remainder. If you enter the values and MOD function as shown in the next screen shot, the MOD function returns the value 1. Six goes into twenty-five four times with one left over.


A formula such as =MOD(ROW(),2) will return a zero whenever the row number is evenly divisible by two, i.e. an even number. Knowing this, you can shade every other row of a selected area using the Conditional Formatting command.

Select a section of the worksheet, or the entire worksheet, and then make the following choices from the Home tab of the Excel ribbon. You enter the formula in the New Format Rule dialog box and then click on the Format button to pick the fill color (i.e. shading). If you are printing on a black and white laser, very light gray will probably work best. If you are printing on a color laser, you can let your imagination run wild.


After clicking the OK button a couple of times, you should see a result similar to the following.


What if you want to change the shading to every third row, or every fifth row? You have to edit the rule for the selected cells using the following options from the same Conditional Formatting icon on the ribbon. The key is that you want to edit the rule, not delete the old rule and create a new rule. You also want to remember to change the formula to reflect how often you want the shading applied.


After clicking the OK, Apply, OK buttons, you should see a result similar to the following.


Although the screen shots in this post are from Excel 2010, the procedure will be similar in Excel 2003, 2007, and 2013.

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd

Word Browse Object

In addition to the regular ways of navigating a Word document with the mouse or keyboard, Microsoft has provided the Browse Object. The Browse Object are the two double-arrows located at the bottom of the vertical scroll bar.


These double-arrows move the insertion point to the previous or next instance of a particular object. The default object browsed is Browse By Page. Therefore, these double-arrows move back and forth between pages of the document.

What most Word users do not know is that you can change the object being browsed to something other than page. For example, if you changed the Browse Object to Browse by Graphic, the double-arrows would then move the insertion point back and forth between all of the graphic images contained in your document.

To change the Browse Object, either click the round button between the double-arrows as shown in the next screen shot, or press ALT+CTRL+HOME on the keyboard.


This brings up a small box with twelve icons. The first two icons do not actually modify the Browse Object. They both display the Find and Replace dialog box, although with a different tab having the focus. The remaining icons change the object being browsed to one of the following.

  • Browse by Edits
  • Browse by Heading
  • Browse by Graphic
  • Browse by Table
  • Browse by Field
  • Browse by Endnote
  • Browse by Footnote
  • Browse by Comment
  • Browse by Section
  • Browse by Page

To learn which icon handles which object, hover your mouse over the icon. The object type will be displayed above the icons as shown in the next screen shot.


The Browse Object can be a tremendous time saver when navigating large, complex documents. The key is to remember that it even exists!

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd

Fix Windows With Linux

What do you do when a Microsoft Windows PC starts acting strange? Perhaps it has been infected with a virus or the hard drive is beginning to fail.

Ironically you can use the Linux operating system to rescue the PC.

As long as you are able to create a bootable CD/DVD/USB Drive, you can load Linux and hopefully repair the PC, if not totally, at least to the point where you can recover data files.

This IT News article by Chris Hoffman is an excellent step-by-step guide on fixing your PC with Linux.