Friday, March 21st, 2008

What’s in a name? Auto_Open or AutoOpen

Sometimes you wonder if the folks up at Redmond are laughing at us behind our backs. Really, do they deliberately set out to confuse us or are they just that plain disorganised?

Today my quandary involves Auto_Open and AutoOpen. These are two special macro names. The first, Auto_Open is Excel’s special named macro that runs automatically when the workbook containing it is opened. AutoOpen is the Word equivalent. It makes no sense that one has an underscore and the other doesn’t – it just makes life for us VBA folk a little more confusing than it should be.

The other macros Auto_Close and AutoClose work the same way, Auto_Close is the Excel macro name – call a macro by this name and save it in your workbook and it will run whenever you close the workbook. In Word, the name is AutoClose.

To add to the confusion, PowerPoint doesn’t support either of the naming conventions, in fact, you can’t create auto running macros in PowerPoint the same way you do in Word and Excel. The workaround is cumbersome, you need to create a PowerPoint add-in that includes the Auto_Open subroutine. Load the Add-in and PowerPoint will run the code in Auto_Open it loads and ditto for subroutine called Auto_close – it runs when the add-in is unloaded – which happens automatically when you exit PowerPoint. Learn more about how to do this in this KnowledgeBase article.

Thanks Redmond, we are now officially confused!

Helen Bradley

Tuesday, June 26th, 2007

Word – Click to type blocks

The MacroButton field code in Word is handy for creating “Click Here” blocks in your documents indicating what needs to be typed and where. When you use these blocks, you simply click and type the entry required.

To create a click here block, choose Insert, Field, from the Categories list choose Document Automation and from the Field names list choose MacroButton. In the text area, after the word MACROBUTTON, type:

NoMacro [Click here and type a name]

Click Ok to finish. Provided you’re displaying field code results and not the codes themselves, you’ll should see the prompt text appear inside the square brackets. Save your document. Then, when you open and use the document you can complete the required text by clicking the prompt and type the requested data. Notice when you do this that the field code text disappears and is replaced by your text.

Helen Bradley

Sunday, February 18th, 2007

Autonumber a series of Word documents

Word does not contain any option for automatically numbering a series of documents with a consecutive number – the type of thing you might want to do if you are using Word to create invoices or numbered purchase orders. 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

Monday, February 5th, 2007

Using special characters in Word documents

I write for UK magazines so I often have to use pounds symbols and my mum’s and brother’s surnames have an ‘e’ with an accute accent – unfortunately neither is on my keyboard. So, to make things easier to write, I use a macro to convert a word to a typed character.

Here’s a sample you can use and adapt. It converts the words cents, pounds, degrees and division to their character equivalents and you can use it to replace almost any word or character with another single character, word or phrase of your choosing:

Sub ConvertText()
FindAndReplace “cents”, “¢”
FindAndReplace “pounds”, “£”
FindAndReplace “degrees”, “º”
FindAndReplace “division”, “÷”
End Sub

Sub FindAndReplace(FindThisWord, ReplaceWithWord)
Set EditRange = ActiveDocument.Content
EditRange.Find.ClearFormatting
EditRange.Find.Execute FindText:=FindThisWord, ReplaceWith:=ReplaceWithWord, MatchCase:=0, Replace:=wdReplaceAll
End Sub

Helen Bradley

Saturday, February 3rd, 2007

Sorting a Word list

While it looks like Word has no tool for sorting a list – only for sorting data in a table, this is not the case and the Table sort tool works just fine for sorting data in a list.

To sort a list, such as a list of names, where each name appears on a new line and the last name is separated from the first by a comma and a space, choose Table, Sort. From the Sort by list, choose Field 1 and then, from the Then by list choose Field 2 and click Ok. This sorts the data into order by last name and then by first name where there are two or more people with the same last name.

If the names appear with the first name then a space and then last name you’ll have to tell Word to differentiate between the two names. To do this, select your list and choose Table, Sort, Options and click the Other option and press the Space bar and click Ok. From the Sort by list, choose Word 2 and then, from the Then by list choose Word 1 and click Ok. This sorts the data into order by last name and then by first name where there are two or more people with the same last name.

If you find that Word 2 doesn’t appear as an option in the Sort by list, click Ok to exit the Table Sort dialog and select the option again.

Helen Bradley

Thursday, February 1st, 2007

Align anything in Word

When you want to make sure everything on a Word page lines up properly, display the gridlines.

To do this, first display the Drawing toolbar then choose Draw, Grid and choose the Display gridlines on screen checkbox. Set the Use Margins option to start the grid aligned on the left and top margins. Set the value to 1 for small squares and to 2 or 3 for a larger grid.

If you select the Snap Objects to Grid checkbox all objects will automatically line up against the grid.

If you don’t want this to be the case, press the Alt key as you drag an object and it will be freed from snapping to align to the grid.

Helen Bradley

Wednesday, January 31st, 2007

Snap an Excel range

Take a picture of a range in Excel to insert into Word as a picture or to place as an image in another area on a workbook. To do this, first select the area to snap and hold Shift as you open the Edit menu. Choose Copy Picture, select As shown on screen or As shown when printed and click Ok.

You can now paste the image wherever you desire. This Shift + Edit menu option also works for copying a clip art or other type of image inserted into an Excel workbook.

Helen Bradley

Sunday, January 28th, 2007

Quick Calculations in Word
Older versions of Word included a Calculate option on the Tools menu which let you make quick calculations.

You can add it back into later versions of Word by right clicking any toolbar and choose Customize. Select the Commands tab and, from the Categories list choose All Commands and scroll the Commands list to locate ToolsCalculate. Drag this onto the Tools menu and hold your mouse there until the menu opens and then drop the option into place. If desired, right click it and remove the word Tools from its name so it simply reads Calculate.

Now test it by typing some values eg 24, 25 & 26 and select them. Choose Tools, Calculate and the status bar will display “The result of the calculation is 75”.

If you later click Control + V you will paste the result of the calculation (75) into your document.

To sum a column of numbers, hold Alt as you drag over the column with your mouse then choose Tools, Calculate. It also works inside tables and you can type a more detailed calculations such as 25*25 and it will calculate the result for you (answer: 625)

Helen Bradley

Page 6 of 6« First23456