Tips N Tricks for Microsoft Office

Microsoft Word

---

6 ways to get free information about Word

Hiring someone to train your troops to use Word is a great idea, but there won't always be a trainer nearby. Fortunately, there are a number of ways users can get help, for free.  Chances are it might take a bit of research, but you can usually find the help you need, with a bit of perseverance.

1. [F1]
The first line of defense is [F1]. Press [F1], enter a few descriptive words, such as "change style", or "delete header". Word will display a list of help topics, based on your input. Sometimes this works great and sometimes the results are inconsistent. However, it's the best place to start, because sometimes the answer pops right up!

[F1] is available in all Office applications. You must install Help for these files to be available.

2. Microsoft Answers
Microsoft Answers is a free support site (forum). If you want to search available posts, enter a question in the Find Answer control. If you don't find what you need, click Ask A Question (at the bottom of the page). You have to sign in using your Windows Live ID. If you don't have one, there's a link for that too.

Microsoft Answers supports Office, not just Word.

3. Word MVPs
MVP's are volunteers who share their expertise, worldwide and for free. Microsoft honors those who stand out with the MVP title. MVPs really know their stuff and there are two ways to benefit from their expertise and generosity. First, visit The Word MVP Site. There's a lot of information readily available. If you don't find an answer, click Contact, read the instructions and submit your question. There's no guarantee anyone will respond, but it can't hurt.  However, try to find the answer yourself first. You're probably not going to get a response to a question that's answered by an existing Help file. By all means, please be polite. These folks provide this service for free.

In addition, MVP Web Sites lists current MVP's with links to their sites. You can't submit questions, but you will find valuable information.

4. Microsoft Knowledge Base
A long-time favorite support site is Microsoft's Knowledge Base. This is a huge database of articles that offers how-to instructions, workarounds for bugs, and so on. The articles are a bit dry and sometimes, difficult to follow, but you'll usually find something you can use. There's even an articleon how to use the Knowledge Base!

5. Microsoft Word Help and How-to
Word Help and How-to is another site supported by Microsoft. Use keywords to search the available files. You won't get personalized answers, but you might find just what you need.

6. Listservs
My favorite resource is a listserv; I'm a member of many. If you're not familiar with the term, a listserv is an email server (group). You send messages and other members respond, all via email.Yahoo! Groups is a good place to start, but there are private listservs as well. Search on "Microsoft Word" and see what's available.

It might take a while to find just the right group. In addition, they're a bit like potato chips. Joining one inevitably leads to joining more--you've been warned!

Microsoft Excel

---

Keep users from selecting locked cells in Excel

Most of us create custom workbooks that others update. You probably protect the sheets and unlock only the input cells. That way, users can't accidentally delete or change formulas and other flag values. The worst they can do is enter invalid values.

Unlocking input cells and protecting sheets is a simple enough process, but a truly knowledgeable user can get around it. For those users, there's a simple macro for resetting things. First, let's unlock input cells in the simple sheet shown below.

In this particular sheet, users only need to update two cells: B1 and B2. You'll want to unlock your input cells, as follows, before you protect the sheet:

1. Select the input cells. In this case, that's B1:B2.
2. Right-click the selection and choose Format Cells from the resulting context menu.
3. Click the Protection tab.
4. Uncheck the Locked option.
5. Click OK.

The next step is to protect the sheet as follows:

1. From the Tools menu, choose Protection, and then select Protect Sheet. In Excel 2007 and 2010, click the Review tab | Protect Sheet (in the Changes group).
2. Enter a password.
3. Uncheck the Select Unlocked Cells option.
   
4. Click OK.
5. Enter the password a second time to confirm it.
6. Click OK.

At this point, you can select and change the contents of cells B1 and B2. You can't select any other cells but B1 and B2.

As I mentioned, it won't always matter if a user can select locked cells. On the other hand, the setup I'm suggesting creates an easy-to-follow data entry map. There's no confusion for the user--the only updateable cells are those the user can select.

This much you might already know. What's a bit scary is that a user can quickly undo the selection property as follows:

1. From the View menu, choose Toolbars.
2. Select Control Toolbox.
3. Click the Properties tool.
4. In the properties window, change the EnableSelection property to 0-xlNoRestriction.
5. Click OK.

Users can also access this property via the VBE. In Excel 2007 and 2010, the user can display the Developer tab (via the File | Customize Ribbon route) and click Properties in the Controls group.

After resetting the EnableSelection property to 0, users can select any cell in the sheet, but they still can't alter cell contents, except for the cells you unlocked before protecting the sheet. This doesn't seem all that important, unless your users don't know what they're supposed to do. In this simple sheet, the input cells are clear, but a complex sheet with non-contiguous input ranges will certainly be more confusing.

To reclaim the original settings, include two macros: One that resets the property when the workbook is opened and a second that resets the property when the selection in the sheet changes. Open the Visual Basic Editor and double-click ThisWorkbook in the Project Window. Then, enter the following macro:

Private Sub Workbook_Open()

  'Disable locked cells in IndirectEx sheet.

  Worksheets("IndirectEx").EnableSelection = xlUnlockedCells

End Sub

That macro will reset the property when the workbook is opened. That way, users always start with the right setting. To add the macro that acts on a selection change in the actual sheet, double-click the sheet (by name) in the VBE Project window and enter this macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

  'Reset if user manages to disable enable selection property.

  Worksheets("IndirectEx").EnableSelection = xlUnlockedCells

End Sub

The only difference is the event that executes each macro. The SelectionChange event fires when a user changes the cell selection (only in the specified sheet, not throughout the entire workbook). Users won't notice it at all unless they manage to disable the EnableSelection property (as described earlier). Then, the user will be able to select a locked cell. Doing so will execute the macro, which will reset the property.  The user will be able to select only one locked cell before the macro resets the property.

The truth is the user that's smart enough to get around your locked cells might know how to circumvent your macros-- but they're worth a try.

Microsoft PowerPoint

---

Quick keyboard shortcuts for the Access Navigation Pane

PowerPoint provides a number of pre-defined backgrounds but you might want to use an image of your own. Fortunately, PowerPoint is accommodating; it's easy to repeat a custom image across a slide's background. For instance, the following image is a .png file created in Paint. PowerPoint will have no problem working with it. This file is relatively small at 182 by 282 pixels and 2881 bytes. Work with the smallest files possible.

Once you have an image file, you're ready to insert it, as follows:

1. Right-click a slide's background and choose Format Background.
2. Click the Picture Or Texture Fill option.
3. Click the File button (under Insert From).
4. Use the Insert Picture dialog to locate the file. Select the file and click Insert.
5. Click the Tile Picture As Texture option.
6. Click Close to apply to the current slide. Click Apply to All and then click Close to apply to all the slides in the presentation.

It takes a few more clicks in PowerPoint 2003:

1. Right-click a slide's background and choose Background.
2. Click the dropdown under Background Fill and choose Fill Effects.
3. Click the Texture tab and then click the Other Texture button.
4. Use the Select Texture dialog to locate the file. Select the file and click Insert.
5. Click OK and then click one of the Apply or Apply To All.

To save the background as a separate file, right-click the background and choose Save Background.

The image in this example is to busy to actually use as a background. The busy-ness of this example simply shows how easy it is to work with an abstract pattern. Insert the file as a texture and PowerPoint does all the rest. It couldn't be simpler!