Posts Tagged ‘macros’

h1

Word: List keyboard shortcuts, autotext, and autocorrect entries

May 3, 2017

There are many things you can do to help automate the writing and editing process. Power users of Word use keyboard shortcuts extensively — either the in-built ones that come with Word (e.g. Ctrl+s to save, Ctrl+c to copy etc.), or ones they’ve created themselves. Power users also use AutoText and AutoCorrect to speed up inserting commonly used text, tables, etc. (See the links below for some of the blog posts I’ve written on all these.)

But how do you know what you’ve got available to use? Some of the more obscure in-built keyboard commands are difficult to find listed anywhere, and how do you remember which ones you’ve created for your own use? How can you get a list of these shortcuts, and a list of your AutoText and AutoCorrect entries? Of course, once you have a list you can then save it or print it out to refer to at any time.

I used Microsoft Word 2010 for Windows when writing this blog post — the same (or very similar) information should apply to later versions of Word for Windows. Mac users — you may be able to do this too, but as I don’t own a Mac, I can’t test on a Mac or write about Mac stuff.

List all in-built Microsoft Word keyboard commands

  1. Open a blank Word document.
  2. Go to the View tab and click the large Macros icon.
  3. Click the drop-down arrow for the Macros in list, then select Word commands.
  4. Select ListCommands in the top box.
  5. Click Run.

List all custom keyboard commands

  1. Open a Word document that is based on the template where you know the custom keyboard commands are stored (if stored in Normal, just open a blank Word document).
  2. Go to the File tab and select Print.
  3. Under Settings, change from the default Print All Pages to Key Assignments.
  4. Choose your printer (e.g. PDF if you want a document to save), then click Print.

List all AutoText entries

  1. Open a blank Word document.
  2. Go to the File tab and select Print.
  3. Under Settings, change from the default Print All Pages to AutoText Entries.
  4. Choose your printer (e.g. PDF if you want a document to save), then click Print.

List all AutoCorrect entries

Use the macro described here: http://wordribbon.tips.net/T009084_Printing_a_List_of_AutoCorrect_Entries

See also:

[Links last checked May 2017]

h1

Word: Macro to add a value at the beginning of each row of a table

May 2, 2017

I had a strange request — someone had a really long table in Microsoft Word (some 50+ pages long…) and wanted to add an asterisk (star) in front of the first character in each row, no matter how many rows and columns there were in the table or what the original first character in each row was (some first characters were numerals, but most were upper or lower case letters). Doing this manually was going to take hours, and he had several tables across various documents that he wanted to do this to.

My first thought was a find and replace with wildcards routine, but I quickly figured out that I didn’t have enough knowledge to do that, even if it was possible. My next thought was to add a new first column, populate it with asterisks, then merge the first and second columns of each row — the problem was that merging required selecting each pair of 1st and 2nd column cells for EACH row, then merging, then removing the added paragraph mark created by the merge process. That was going to take as long or even longer than adding the asterisks manually!

So I contacted my friend Dave Gash (www.davegash.com), a US-based programmer/tech writer, to see if he could help. His forte isn’t VBA, but he does know programming logic (and he knows what to search for), and by the next morning he had a solution for me. Actually he had a solution that was TOO good — his first solution (Macro 2 below) added the asterisk to the beginning of all rows in ALL tables in the document. But that’s not what my client wanted — he wanted to add asterisks to all rows in a specific table, not every table. A bit of tweaking and Dave came back a few minutes later with the solution that suited my client (Macro 1 below). When I ran it in test mode, the changes were almost instant, but they took about 5 mins on the 50+ page table! That was still many hours less than it would’ve taken manually. I owe Dave a beer or three!

Macro 1: Add an asterisk to the beginning of all rows in a specific table

Sub AddAsteriskToTableRow()
   ' From Dave Gash www.davegash.com April 2017 '
   ' Collapse the range to start so as to not have to deal with '
   ' multi-segment ranges. Then check if cursor is within a table. '

   Selection.Collapse Direction:=wdCollapseStart
   If Not Selection.Information(wdWithInTable) Then
      MsgBox "You can only run this when your cursor is within a table."
      Exit Sub
   End If

   ' Process every row in the current table. '
   Dim row As Integer
   Dim rng As Range

   For row = 1 To Selection.Tables(1).Rows.Count
      ' Get the range for the leftmost cell. '
      Set rng = Selection.Tables(1).Rows(row).Cells(1).Range

      ' For each, insert asterisk in leftmost cell.'
      ' Change value in quote marks if you want something other than an asterisk. '
      rng.InsertBefore ("*")
   Next
End Sub

Macro 2: Add an asterisk to the beginning of all rows in ALL tables in a document

Sub AddAsterisksToAllTables()
    
' From Dave Gash www.davegash.com April 2017
' Loop through all tables
For tbl = 1 To ActiveDocument.Tables.Count
 
    'Set up row and range vars
    Dim row As Integer
    Dim rng As Range
 
    'Loop through rows in current table
    For row = 1 To ActiveDocument.Tables(tbl).Rows.Count
        ' Get the range for the leftmost (column 1) cell
        Set rng = ActiveDocument.Tables(tbl).Rows(row).Cells(1).Range
        ' Insert the asterisk before the text in leftmost cell
        ' Change value in quote marks if you want something
        ' other than an asterisk.
        rng.InsertBefore ("*") 
    Next row 
Next tbl 
End Sub

Notes

  • The examples above use an asterisk as the added character, but you can make that character anything you want — one or more letters or numbers or standard punctuation symbols (or a combination), with or without a space. Just change the value in the double-quotation marks in the macro to what you want (e.g. you might have a list of product numbers that need to have ‘MQ’ added in front of them — change “*” to “MQ” [or “MQ-” or “MQ “]). You might want to change the name of the macro too, to reflect what you’re adding (e.g. AddMQToTableRow).
  • Don’t run this twice in the same table, otherwise you’ll end up with two asterisks!
  • Dave asked me to add this acknowledgement: ‘My source was from a StackOverflow (the coder’s friend, believe me!) member (apparently also from Western Australia!). … I modified the code I found there for your purposes.’ Source: http://stackoverflow.com/questions/7226721/how-can-you-get-the-current-table-in-ms-word-vba

[Links last checked May 2017]

h1

Word: Find and highlight multiple words at once

November 6, 2015

This is a variation on the multiple find/replace macro covered here: https://cybertext.wordpress.com/2015/03/03/word-macro-to-run-multiple-wildcard-find-and-replace-routines/. I suggest you read that post first (and test it) before attempting this one. This post assumes you can already do what’s in that earlier post.

Scenario

The scenario for this one is a little different — the author wanted to find various words in a document and highlight them for probable change. As there were MANY documents she had to process, she needed an easy way to find these words and highlight them. She didn’t want to change them at this stage. The words related to project names, company names, facility names, document number prefixes, etc. One project’s documents were to be the basis of a set of documents for another project in the same company, so one of the tasks was to ‘personalize’ the copies of the original project’s documents with the names used by the new project.

Solution

Although there may be several ways you can do this, I decided to stick with what I already knew. I figured that using a variation of the ReplaceTableWithList macro (as discussed here: https://cybertext.wordpress.com/2015/03/03/word-macro-to-run-multiple-wildcard-find-and-replace-routines/) and a new table called by that macro variation should solve it. And it did.

Step 1: Create your find/replace table

  1. Start a new Word document, and create a two-column table in it.
  2. In the left column, type in the words/phrases you want to find, each on a different row.
  3. In the right column, type \1. (This means that the wildcard find/replace will replace what was found with itself — remember, you’re only trying to identify the words that *may* need changing at this point, not changing them.)
  4. Save the document to this folder, noting the name of the file as you’ll need that later:
    C:\Users\<your_username>\AppData\Roaming\Microsoft\Word\STARTUP.

Step 2: Set up the macro to work with the table

Add the macro below to an existing document, or better, an existing template, or even better, a central macros template that loads whenever you open Word.

Once you’ve added it, change the line that starts with sFname and has the file path — that path points to MY file on MY computer. You need to change it to YOUR file name and YOUR file path.

NOTE: Copy all the code below to the clipboard — it goes off the page, so don’t type it out as you’ll miss some of it or could make a typo.

Sub ReplaceFromTableListName()
' from Doug Robbins, Word MVP, Microsoft forums, Feb 2015, based on another macro written by Graham Mayor, Aug 2010
Dim oChanges As Document, oDoc As Document
Dim oTable As Table
Dim oRng As Range
Dim rFindText As Range, rReplacement As Range
Dim i As Long
Dim sFname As String
'Change the path in the line below to reflect the name and path of the table document
sFname = "C:\Users\rhonda\AppData\Roaming\Microsoft\Word\STARTUP\find_and_replace_routines_names_macro.docx"
Set oDoc = ActiveDocument
Set oChanges = Documents.Open(FileName:=sFname, Visible:=False)
Set oTable = oChanges.Tables(1)

' Make sure highlight is set to the colour you want, e.g. wdYellow, wdBrightGreen, wdPink, wdTurquoise
Options.DefaultHighlightColorIndex = wdTurquoise

For i = 1 To oTable.Rows.Count
 Set oRng = oDoc.Range
 Set rFindText = oTable.Cell(i, 1).Range
 rFindText.End = rFindText.End - 1
 Set rReplacement = oTable.Cell(i, 2).Range
 rReplacement.End = rReplacement.End - 1
 Selection.HomeKey wdStory
 With oRng.Find
 .ClearFormatting
 .Replacement.ClearFormatting
 .MatchWildcards = True
 .Text = rFindText.Text
 .Replacement.Text = rReplacement.Text
 .Replacement.Highlight = True
 .Forward = True
 .Wrap = wdFindContinue
 .Execute Replace:=wdReplaceAll
 End With
Next i
oChanges.Close wdDoNotSaveChanges
End Sub

Step 3: Test that it works

After setting up the ReplaceFromTableListName macro (above), run it on a test document — copy an existing document and test on the copy to make sure you don’t mess up anything.

NOTES:

  • If you get an error message, check that you have the correct file name and path in the macro, AND check that your Word document containing the table that’s called by the macro has no empty rows.
  • If none of the words in your table get highlighted in your document on the first pass, select a highlight colour from the Home tab as though you were going to highlight manually, then run the macro again.
  • If some words are missed, check the table containing them — if a word has an initial capital in the table, but not in the document you are searching (e.g. in a URL), then the macro won’t highlight it. For words that could be capitalized in various ways, either add new lines for each variation, OR search for the part of the word you know won’t be capitalized (e.g. if you were searching for ‘Amazon’, then change the search term in the first column to ‘mazon’ to pick up the word whether it has an initial cap or not).
  • Some areas of your document won’t get highlighted, even if they contain the words you’re looking for — e.g. headers and footers, text boxes. You’ll have to check for these manually.

My author was VERY happy — she had something that only took a few seconds to run and highlighted all the various words she needed to look for.

[Links last checked November 2015]

h1

Word: Macro to run multiple wildcard find and replace routines

March 3, 2015

After some digging around, I’ve found a way to save myself hours of running separate find and replace routines using wildcards in Microsoft Word.

The routines I commonly use already save me time, but now I’ve got one macro that runs them all at once — currently 110 of them that are just for replacing an ordinary space with a non-breaking space!

I didn’t write the macro — that honor goes to Microsoft Word MVPs Graham Mayor who wrote the initial macro, and Doug Robbins who helped me tweak it to do what I wanted it to do. Details are here: http://answers.microsoft.com/en-us/office/forum/office_2003-word/macro-for-performing-more-than-one-find-and/bd931bf7-5ebe-4650-924c-d15c9512129c?page=1

NOTE: EditTools software (http://wordsnsync.com/edittools.php) has a neat interface for setting up your wildcard find and replace routines, along with a facility to set up a script to run them all, and I use that on my own PC. However, I’m not allowed to install that software on my work PC (long story…), so I wanted to figure out a way to emulate what an EditTools script does by creating a macro to achieve the same end.

UPDATE: PerfectIt software (v3 onwards; http://intelligentediting.com) can be customized to include wildcard find/replace routines too, without you having to know anything about macros.

Before doing these steps, you need to be familiar with:

  • accessing the VBA area of Word
  • using wildcards to find and replace in Word.

I won’t cover either of those things in this post.

Step 1: Create a table of your wildcard find/replace routines

The first stage in getting this to work is to create a 2-column table in Word, listing what you want to find in the left column and what you want to replace it with in the right column. Use one row for each find/replace routine. Use the same syntax as you would for a wildcard find/replace.

This table will take a while to set up. You should test each wildcard find/replace routine on a document BEFORE you add it to the list, just to make sure it works correctly and doesn’t mess up anything else. These find/replace routines will all be done as ‘replace all’, so you will NOT have the opportunity to accept some and reject others. Testing is crucial to make sure you don’t replace something you shouldn’t.

In the example below, I’ve set up routines for replacing a standard space between a number and a month of the year with a non-breaking space (^s) between those two elements, as well as replacing a standard space between a number and a unit of measure (e.g. lux, nm) or a word that often follows a number in the documents I work on (e.g. fish, indiv [for ‘individual’ or ‘individuals’). As I said, I have some 110 of these…

widlcard_fr_macro01

Once you’ve created your table, save it to a location that your macro can pick it up from easily — I suggest somewhere on your local drive, not a network location (unless you intend sharing it with others). I put mine in my Word > STARTUP folder, which is where I keep my macros.dotm file.

Step 2: Set up the macro to work with the table

Add the macro below to an existing document, or better, an existing template, or even better, a central macros template that loads whenever you open Word.

Once you’ve added it, change the line that starts with sFname and has the file path — that path points to MY file on MY computer. You need to change it to YOUR file name and YOUR file path.

NOTE: Copy all the code below to the clipboard — it goes off the page, so don’t type it out as you’ll miss some of it or could make a typo.

Sub ReplaceFromTableList()
' from Doug Robbins, Word MVP, Microsoft forums, Feb 2015, based on another macro written by Graham Mayor, Aug 2010
 Dim oChanges As Document, oDoc As Document
 Dim oTable As Table
 Dim oRng As Range
 Dim rFindText As Range, rReplacement As Range
 Dim i As Long
 Dim sFname As String
 'Change the path in the line below to reflect the name and path of the table document
 sFname = "C:\Users\rhonda\AppData\Roaming\Microsoft\Word\STARTUP\find_and_replace_routines_macro.docx"
 Set oDoc = ActiveDocument
 Set oChanges = Documents.Open(FileName:=sFname, Visible:=False)
 Set oTable = oChanges.Tables(1)
 For i = 1 To oTable.Rows.Count
     Set oRng = oDoc.Range
     Set rFindText = oTable.Cell(i, 1).Range
     rFindText.End = rFindText.End - 1
     Set rReplacement = oTable.Cell(i, 2).Range
     rReplacement.End = rReplacement.End - 1
     Selection.HomeKey wdStory
     With oRng.Find
             .ClearFormatting
             .Replacement.ClearFormatting
             .MatchWildcards = True
             .Text = rFindText.Text
             .Replacement.Text = rReplacement.Text
             .Forward = True
             .Wrap = wdFindContinue
             .Execute Replace:=wdReplaceAll
       End With
 Next i
 oChanges.Close wdDoNotSaveChanges
End Sub

Step 3: Test that it works

After setting up the macro, run it on a test document — copy an existing document and test on the copy to make sure you don’t mess up anything.

As my file had 110 find/replace routines for changing some normal spaces to non-breaking spaces, I needed to test it on a document that had many normal spaces for the elements I wanted to change to non-breaking spaces. I opened a copy of an existing 400p Word document, used Word’s normal find/replace to replace all existing non-breaking spaces with a normal space (some 3000 of them in that document I tested it on!). Then I ran the ReplaceFromTableList macro to see if it worked.

It did. Beautifully. And it took about 3 minutes to run ALL those routines on that 400p document. The end result was a document that had more than 5000 non-breaking spaces added to it (I checked the number by taking them all out again and replacing them with normal spaces!).

One 400p Word document, 110 separate find/replace routines. In 3 minutes. In one macro.

That’s gotta be a win for automating tedious, routine tasks! Now, to think about what other find/replace routines I can add to the table…

[Links last checked March 2015]

h1

Word: Macro to replace some hyphens with en dashes

May 16, 2014

I run several find/replace routines on most of the documents I edit. One is to replace <space><hyphen><space> with <space><en dash><space>, and another is to find any number range separated by a hyphen not an en dash (e.g. 2013-2014 or p35-47), and replace the hyphen with an en dash (e.g. 2013–2014, p35–47).

So I decided to automate this process by recording a macro for each find/replace routine, then I combined those macros into one. I could assign a keystroke combination to it, but as I only run it once on each document, there’s probably not a lot of value in doing that. The whole process takes just a few seconds, even on a long document.

CAUTION: If you have numbers legitimately separated by hyphens (e.g. document numbers like 1234-567-890) this macro will replace the hyphens in those numbers with en dashes too, which may not be what you want. A workaround if you only have a few numbers of this type is to run this macro, and then manually change those few it replaced incorrectly.

Here’s the macro:

Sub ReplaceHyphenWithEnDash()
'
' ReplaceHyphenWithEnDash Macro
'
' The first part of this macro replaces space hyphen space
' with space en dash space
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = " - "
        .Replacement.Text = " ^= "
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchWildcards = False
        .MatchSoundsLike = False
        .MatchAllWordForms = False
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
' The second part of this macro finds a number range separated 
' by a hyphen and replaces the hyphen with an en dash
    Selection.Find.ClearFormatting
    Selection.Find.Replacement.ClearFormatting
    With Selection.Find
        .Text = "([0-9])(-)([0-9])"
        .Replacement.Text = "\1^=\3"
        .Forward = True
        .Wrap = wdFindContinue
        .Format = False
        .MatchCase = False
        .MatchWholeWord = False
        .MatchAllWordForms = False
        .MatchSoundsLike = False
        .MatchWildcards = True
    End With
    Selection.Find.Execute Replace:=wdReplaceAll
End Sub

There may be a simpler way to write this macro, but as I’m not a VBA developer and as this worked for me, I’ll leave it as it is!

h1

Word: Resize all images in a document to the same width

February 7, 2014

Note:

  1. This post is an adaptation of an article by Helen Bradley in Australian Personal Computer magazine (January 2014 issue, p99). Full credit and kudos should go to Helen and APC for this information — I only tweaked it and expanded upon it to suit my purposes, and added full steps and screen shots.
  2. You should be familiar with writing macros using the VBA code editor in Word. This post will not take you step-by-step through that process.
  3. Read the warning notice below.

BEWARE! This macro WILL resize ALL images in your Word document to the same width — that includes any cover art, logos, images on landscape pages, etc. This may NOT be what you want and you may decide that this macro isn’t for you. As with any such global change, test it on a COPY of your original document before using it on your original. You have been warned.

What you will do:

  1. Create a form that any user can complete when they run the macro that lets them specify the width (in centimetres) they want for all images and inline shapes.
  2. Add the code to the form.
  3. Test the macro on a COPY of an existing document.

Step 1: Create the form

  1. Open the Visual Basic editor in Word.
  2. Select Normal in the Project Explorer.
  3. Select Insert > UserForm from the menu.
  4. Add these elements to the form — TWO command buttons, ONE spin button, TWO labels, and ONE text box. You’ll arrange them later (Step 12).
  5. Open the properties of the spin control and set these values: Max: 160 (this will allow a maximum width of 16 cm; sorry — I don’t know how to set this for inches); Min: 20 (minimum width of 2 cm); Small change: 5 (increments of 0.5 cm); Value: 50. Adjust these values to suit your circumstances (e.g. if you have a 2-column document and only want small widths for the images, set the Max to 75 [i.e. 7.5 cm] or similar).
  6. Open the properties of the text box and set the Value to 5 (this means the default displayed in the text box will be 5 cm; change it to suit your circumstances). If you only want your users to use the spin control to set the value and don’t want to let them change it in the text box, set the Locked property to True.
  7. Open the properties of one of the labels, delete the text in the Caption field and add some explanatory text there — see the screen shot below for my example.
  8. Open the properties of the other label and change the Caption text to cm (for centimetres).
  9. Open the properties for CommandButton1 and change the Caption text to Resize Images.
  10. Open the properties for CommandButton2 and change the Caption text to Exit.
  11. Open the properties for UserForm1 and change the Caption to Resize all images.
  12. Arrange all elements on the form to follow familiar GUI principles (e.g. explanatory text at the top, ‘cm’ label to the right of the text box and spin control to the right of that, command buttons at the bottom of the form etc.).

reszie_all_01

Step 2: Add the code

Now that you have your form, you can add the code that sits behind the various elements.

Spin Control code:

Double-click the spin control and add this line between the Sub and End Sub lines: TextBox1.Value = SpinButton1.Value / 10

You should end up with this:

reszie_all_02

Exit button code:

Double-click the Exit button and add this line between the Sub and End Sub lines: Unload me

You should end up with this:

reszie_all_03

Resize Images button code:

Double-click the Resize Images button and add these lines between the Sub and End Sub lines — make sure you use the exact case and punctuation as listed here (Note: Some lines shown below will wrap in WordPress — check the screen shot to see where the lines break; I’ve left these lines in as text so you can copy/paste them):

Dim insertedPicture As InlineShape
Dim insertedShape As Shape
Dim imgMult As Single
imgMult = TextBox1.Value * 28.34

For Each insertedPicture In ActiveDocument.InlineShapes
insertedPicture.Select
insertedPictureHeight = insertedPictureHeight * imgMult / insertedPicture.Width
insertedPicture.Width = imgMult
Next

For Each insertedShape In ActiveDocument.Shapes
insertedShape.Select
insertedShape.Height = insertedShape.Height * imgMult / insertedShape.Width
insertedShape.Width = imgMult

Next

Unload Me

You should end up with this:

reszie_all_04

Notes:

  • I’m not sure what the multiplication factor of 28.34 means, but I suspect it’s something to do with converting the underlying measurement units (twips? points?) in Word to centimetres.
  • The aspect ratio of the image is kept; when the macro resizes the width, it keeps the proportions for the height.

Code to open the form:

Under the Normal > Modules folder in Project Explorer, add this code to NewMacros:

Sub resizeImages()

UserForm1.Show

End Sub

It should look like this:

reszie_all_05

Step 3: Test

As I mentioned at the beginning of this post, you’ll need to test this macro to see if it suits your purposes. If you have images in your document that SHOULDN’T be resized, then either consider not using this macro at all, or using it for everything, then manually sizing those few images back to their original dimensions.

  1. Run the resizeImages macro on a COPY of your document.
  2. Check ALL images in your document to see if any changed that you didn’t want to change (look for such things as logos, cover page art elements, images in landscape orientation that are now much narrower than you wanted, etc.).
  3. You can undo the changes the macro made by clicking the Undo button as many times as necessary to undo the changes to all images.

***************

Finally, I again wish to acknowledge Helen Bradley‘s work in creating the original version of this macro and APC for publishing it. Please give thanks to her, not me.

h1

Word: True title case

February 4, 2014

In Word 2003 and earlier, one of the case options was ‘title case’ where all main words were capitalized (capped), but minor words (e.g. the, a, and, etc.) weren’t. But since Word 2007, the only case options when changing case automatically are:

  • sentence case (only the first letter of the first word in the sentence is capped)
  • all upper or all lower case
  • toggle case (who uses that?)
  • capitalize each word (which means exactly what it says, so all the ‘little’ words get capped too!).

There’s no true title case anymore. However, the good folks over at WordTips have provided a macro that works around the problem by specifying that certain words in a selected piece of text are NOT to be capitalized.

You can get the details here:

I tested the macro and it works well. I also added my own words to the list and it still worked well ;-) Just make sure you separate each word you add with a space. I also added my additional words in alphabetical order, just in case that made any difference.

And if this macro is something you’ll use a lot, add a Quick Access Toolbar icon for it.

NOTE: The macro doesn’t change the capitalization of first word of the selected text — the wrd = 2 part of the macro tells the macro to start at the second letter of the selected text, thus ignoring first words in sentences like ‘The’ that should legitimately be capitalized. If you want the macro to include ALL words in the selection — even the first word in a sentence — then change that part of the macro to wrd = 1.

[Links last checked February 2014]