Monday, March 12th, 2007

Joining data in Access queries

When your Access query results need to look neat and tidy, you’ll often want to join related pieces of data together that actually appear in separate fields of the file. For example if you have a list of first names and last names in separate fields (as you should), you can still view them in a single column in your query results.

To do this, join the data in the query data sheet, for example this will join the data from the FirstName and LastName fields and display it as a proper name in the query results:

FirstName & ” ” & LastName

Helen Bradley

Sunday, March 11th, 2007

Keeping watch in Excel

Big worksheets are a bit of a nuisance to work with. I typically find myself working in one area but wanting to see what is happening in another.

To do this, I set up a watch value that tracks the figure I’m most interested in and which is off the screen. To do this, right click the cell you’re interested in seeing and choose Watch to set the cell as one to watch. Now, regardless of where you are in your worksheet, you can see the value in the ‘watched’ cell in the Watch window – double clicking its title bar anchors it on the screen.

In earlier versions of Excel you could create your own watch window using a text box. Draw it using the Text Box button on the Drawing toolbar, click inside it, press F2 and type the cell to watch, eg =Z100 – simple and very effective.

Helen Bradley

Saturday, March 10th, 2007

Color code Outlook 2003 appointments

I’m a visual person so I like to see things and I use the Outlook calendar to plan my day. But some appointments are really important and some I can miss if I need to.

So, I like to color code my appointments so I know at a glance what has to be done. I show personal appointments as one colour, online seminars and things that would be nice to attend but which I don’t have to attend as another color and stuff I have to do as yet another color.

To do this, open the Calendar and select the Calendar Coloring button on the Standard toolbar. Choose Edit Labels and, opposite each color make sure the descriptions for the types of appointments match those you want to use and, if not, change them (you only need to do this step once).

Then, to color code an appointment click the Label drop-down menu and choose the color to use – simple but very effective!

Helen Bradley

Friday, March 9th, 2007

PowerPoint preview and edit

I’m one of those people who love to do multiple things at one time. I can listen to a movie, work and have the washer and dryer chugging away in the background while I’m planning a weekend art project.

That’s why I love today’s tip. It lets you preview a PowerPoint presentation at the same time as you edit it.

Simply, choose Slide Show and while clicking View Show, hold the Control key. You’ll see your slide show on the screen in the top left corner and PowerPoint visible in the remaining area. You can work on the show and preview it (and your changes) the same time.. neat idea?

Helen Bradley

Friday, March 9th, 2007

Cool moves in Publisher

Here are some of my favorite tips for getting things lined up in Publisher 2003:

To move a number of objects together, select them and choose Arrange, Group from the menus or click the Group button which appears when the objects are selected. You can now move and size the objects all at once. Choose Arrange, Ungroup to ungroup them again.

The Nudge and Measurement menus help you move objects in small increments. Choose Arrange, Nudge and tear off the Nudge menu to have access to the buttons. The measurement toolbar (View, Toolbars, Measurement) helps you position objects with pinpoint accuracy.

To create a ruler guide, position your mouse over the horizontal or vertical ruler bar and, when the mouse changes shape, drag the ruler guide into position. To display rulers, choose View, Rulers. Use these guides as an aid for lining up objects on your page. Right click the guide to access other options.

Helen Bradley

Wednesday, March 7th, 2007

Click and go in Word

You already know that you can simply double click anywhere in the Word editing window and immediately the cursor appears ready for you to start typing. But clicks elsewhere do practical and timesaving things too:

  1. Double click a ruler (vertical or horizontal) to open the Page Setup dialog
  2. When you have a header or footer set up, double click it to go to Header and Footer view
  3. Double click the indent marker on the horizontal ruler to open the Paragraph dialog
  4. Double clicking a bullet or number in a list opens the Bullets and Numbering dialog

    and, finally for today at least:

  5. Double click on the column marker in the ruler when you’re inside a table (or double click the table’s move handle) to open the Table Properties dialog.

Helen Bradley

Wednesday, March 7th, 2007

Grouping AutoText entries in Word

Yesterday I explained how to use AutoText entries to speed up entering data. Today, I’ll show you how to organize your AutoText entries into groups on the menu using Styles:

You create or add an entry to AutoText groups on the AutoText menu by saving an AutoText entry formatted using a style with the name you want to be added to the menu. For example, to add an AutoText entry to the Closing group, type the text, select it and format it with a style called closing (or create a style with that name). Then save the text as an AutoText entry.

When an AutoText entry is saved that is formatted with a style a new entry with that style name appears on the AutoText menu or the item is added to the submenu if it already exists. So, to enter something on the closing submenu, format it with a style that you have called Closing before creating it.

Helen Bradley

Tuesday, March 6th, 2007

AutoText in Word

The AutoText tool in Word can help speed up your work by automatically inserting pieces of text and images. Here’s how to use it:

Step 1
To create an AutoText entry, type and then select the text to save and choose Insert > AutoText > New, type the name for the entry and click Ok. You can also do the same thing with images inserted into a document and create an AutoText entry from one.

Step 2
To later use a saved AutoText entry, type the name of the entry. When you have typed sufficient letters to uniquely identify the entry (at least four), Word will prompt you with the AutoText name. Press Enter to insert the AutoText entry. You can also type the name and press F3 or select the entry by choosing Insert, AutoText, AutoText and choose it from the dialog.

tomorrow… how to create your own groups in AutoText to organize entries.

Helen Bradley

Monday, March 5th, 2007

Autonumbering documents in Word

Word does not contain any option for automatically numbering a series of documents with a consecutive number. The solution is to create a macro to do the work for you. Start with a template that has a macro that runs when ever the template is used for a new document. The macro should read a number stored in a file on your drive, add it to your document and then, to prepare the number for the next time it’s required, the number should be incremented by one and be written back into the file.

To create the solution, create a new document (or open an existing one to use as a template) and click where you want the sequential number to appear, and choose Insert, Bookmark, type docNum in the Bookmark name area and click Add.

Save this file as a template by choosing File, Save As, from the Files as type list choose Document Template (*.dot), give the file a name and click Save. With the file still open, choose Tools, Macro, Macros and type the name of the macro docNum and, from the Macros In list choose the template file name for the file you just saved and choose Create.

Type this macro as shown, the sub and end sub lines should be there already:

Sub docNum()
Dim MyString, docNumber
FileToOpen = “c:\windows\docNumfile.txt”
Open FileToOpen For Input As #1
Input #1, docNumber
Close #1 ‘ Close file
ActiveDocument.Bookmarks(“docNum”).Select
Selection.InsertAfter Text:=docNumber
docNumber = docNumber + 1
Open FileToOpen For Output As #1
Write #1, docNumber
Close #1 ‘ Close file.
End Sub

Now choose File, Close and Return to Microsoft Word. With the template on the screen, choose File, Close and answer Yes when prompted to save your changes.

Now open Notepad and type a number 4 or 5 numbers less than the number of the first quote you want to use. So, if you want to start numbering at 200, type 195 so you have a few numbers to use to test the process. Choose File, Save As and save the file as a text file, calling it docNumfile.txt and save it to this folder: C:\windows. Close Notepad

To test the process, choose File, New, choose the template file and click OK. Now run the macro by choosing Tools, Macro, Macros, docnumb, Run. If you have everything right the document number will be inserted in the document.

When this is working fine, alter the macro so this process of inserting the document number happens automatically whenever you create a new document based on this template. To do this, choose Tools, Macro, Macros, click on docnumb and click Edit. Change this macro’s procedure name by altering this line of code:

Sub docnumb()

to read

Sub AutoNew()

Choose File, Close and Return to Microsoft Word. With the template on the screen, choose File, Close. Say No to saving your changes to this file but answer Yes to save the changes to your template file.

Now test again by creating a new file using File, New, choose your template and click OK. The document number should be added automatically to the new document.

If, in the process of testing you find you go past your starting document number, open Notepad and open the file docnumfile.txt, type a new starting number and save it again.

Helen Bradley

Sunday, March 4th, 2007

Use the PowerPoint menu to go back

By default, when you are viewing a PowerPoint presentation pressing the right mouse button shows the PowerPoint presentation menu.

If you’d prefer that, instead, it moved back one slide, then you can configure it to do this. Choose Tools, Options and click the View tab. Clear the Show menu on right mouse click checkbox and click Ok.

Now, the left mouse takes you forward and the right takes you back. If you need to see the menu at any time, pressing Shift + F10 will display it.

Helen Bradley