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

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…]

Displaying Excel Formulas

Normally when you enter a formula into Microsoft Excel you want to see the result of the formula, not the formula itself. However, a situation may arise where you need to see the formulas in a spreadsheet and not the answers to the formulas. This need usually occurs when you have to ‘debug’ a problem spreadsheet.

The easiest way to look at a formula is to move the cell pointer to a cell with a formula and look at the formula bar as shown in the next screen shot.


However, this method is only effective if you have a spreadsheet with very few formulas. When you are working with a large spreadsheet it may be difficult to to find each formula. The following spreadsheet, while not large, has a lot of formulas. In fact, only three of the cells displaying numbers are actual data entry cells. Everything else is a formula.


The easiest way to display formulas in Excel is to hold the CTRL key and press `. {The key to the left of the 1 key on the keyboard.} This toggles the display between showing formulas and showing results. Starting with the 2007 version of Excel Microsoft added an icon to show formulas. It is logically placed (surprisingly) on the Formulas tab on the ribbon. This icon is also a toggle: alternating between displaying formualas and results.


Using either method on the demonstration loan comparison spreadsheet displays all of the formulas, including an extra one in cell A10 that was not displayed due to the formatting applied to that cell.


Note: The keyboard technique also works for LibreOffice Calc.

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd

Hide The Microsoft Office Ribbon

When Microsoft released Office 2007 it replaced the menus and toolbars that everyone knew and loved with the ribbon. According to Microsoft the ribbon was more logical then the menus/toolbars and easier to learn. While that may or may not be true, millions of Office users had learned where the commands were located, at least for those that they used on a regular basis. These people were actually less productive when using Office 2007 because they had to re-learn the command locations. Many people could not figure out how to preview or print a document because Microsoft had removed the File menu and had replaced it with the ‘Microsoft Office Button’.


Office 2010/2013 continues with the ribbon, however, Microsoft has mercifully killed the Microsoft Office Button and brought back the File menu, although it is now called ‘Backstage View’. As you can see in the following Excel 2010 example the Backstage View does most of what the old File menu did, along with additional features.


Depending on the size of your monitor and the screen resolution, the ribbon in either of these versions takes the equivalent of four or five rows of screen ‘real estate’. To power users who already knew the old menus, this was just another reason for hating the ribbon. What many users do not know is that you can turn the ribbon display off, while the functionality is available at the click of the mouse. (Although the following sample screen shots are from Excel 2010, the procedure is the same in the other Office programs for the 2013, 2010 and 2007 versions.

To turn off the display of the ribbon, double-click one of the tab names. The ribbon disappears, with only the tab names remaining.


When you need to access any of the ribbon commands, click the appropriate tab name. The ribbon is temporarily re-displayed ‘over’ your document. [Note: that you cannot see rows 1 through 2 in the following screen capture.]


The ribbon is then re-hidden after performing the chosen task.

To turn the ribbon display on, double-click one of the tab names.

You can also try this tip on any other programs you use that have adopted the ribbon concept. For example, this tip is known to work with the SnagIt 9 and CorpTax programs.

View VBA Consultants profile on LinkedIn

Copyright 2014 – VBA Consultants Ltd

Customizing the Office 2010/2013 Ribbon

The biggest change in Microsoft Office 2007 was the replacement of the familiar menu/toolbar structure with ‘the ribbon’. This change was so radical that many corporations did not switch to Office 2007 in order to avoid the massive retraining costs. The most common complaint from users that had to switch to Office 2007 was “I can no longer find the commands that I used to know by heart.”

One of the useful features of Office 2010/2013 is the ability to modify the ribbon. By modifying the ribbon you can place the icons for your most used Office commands into a separate (i.e. new) tab, or you can add a group to an existing tab. You can even group the icons in a manner similar to the out-of-the-box ribbon. You also have the ability to stop the display of one or more of the built-in tabs.

[Read more…]

Customize the Office Quick Access Toolbar

The Quick Access Toolbar (QAT) was Microsoft’s attempt to appease users of Office 2003 for the replacement of the menus/toolbars with the Ribbon that debuted in Office 2007. The QAT allowed users to add their favorite command shortcuts to one location rather than having to search the ribbon for commands that were no longer in the expected location. The QAT is the series of icons that are (by default) displayed above the tabs of the Office ribbon. The default QAT for Word 2010 is shown below.


To add the commands that you need to the QAT, click the drop-down arrow at the right end of the QAT and select More Commands from the menu. NOTE: Although our example is using Word, the procedure is the same for all Office applications that have the QAT.


This will open the Word Options dialog box as seen in the next image. NOTE: this post uses images from Office 2010. The dialog boxes look slightly different in Office 2007.


The left drop-down allows you to narrow the area from which you wish to choose a command. Use All Commands for a comprehensive list.


The right drop-down allows you to customize the QAT for all documents, or just the current one. This could be very useful if a document that you use on a regular basis requires special commands.


The basic procedure is to select a command from the left pane and move it to the right using the Add >> button. Do not to forget to add the Separator to add vertical bars to the QAT. Use the << Remove button to remove commands from the QAT. The up/down arrows along the right side of the dialog box can be used to arrange the icons left-to-right.


The QAT will be look like the following after clicking the OK button on the previous dialog box.


You can go through the procedure again to make any additional modifications. You can also erase all of your customizations and start from scratch if you click the Reset button on the options dialog box.




View VBA Consultants profile on LinkedIn

Copyright 2013 – VBA Consultants Ltd

Office 365 for Non-profit Organizations

Office 365 is Microsoft’s cloud version of the venerable Office suite of programs. Instead of installing Office on your computer, you use a web browser to access the programs. Your data is stored either in the cloud via Skydrive or on your local machine.

According to Microsoft the main advantages for users are:

  • not worrying about updates,
  • not having to install Office on each computer,
  • eliminating the possibility of having users with different, maybe incompatible, versions of Office,
  • ability to use Office from anywhere with an internet connection (think tablet users),
  • potential for world-wide group collaboration on documents,
  • not having to worry about security (if you trust the ‘cloud’), and
  • not having to purchase an Office license for each computer.

Instead of purchasing a license for each computer, an organization subscribes to Office 365 much like you would subscribe to a magazine: $ per user per month. The advantage to Microsoft is that it receives a more consistent stream of revenue.  Face it – even though a user may be completely satisfied, Microsoft is not making a dime from someone using Office 97 on a Windows XP machine.

Realizing that non-profit organizations may have very tight budget constraints, Microsoft has announced that qualifying non-profits can receive the basic E1 version of Office 365 for free through its software donation program. Groups that need a little more ‘muscle’ for their applications can obtain the E3 version for $4.50 per user per month rather than the regular price of $20.00 per user per month.

This is the link to the official Microsoft press release.

You can find more information about what is included with Office 365 for non-profit organizations here.