An introductory DIY Word macro project for Windows
This post is intended to give you a brief insight into what can be done with Visual Basic for Applications (VBA) code – otherwise known as ‘macros’. VBA is built into all of the Microsoft Office applications, and allows you to automate nearly all of their functions. It also enables them to exchange data and to control one another. This means, for instance, that you could make an Excel spreadsheet control Word so that it produced a formatted report for each of the entries in a worksheet. The combined system would run as fast as your computer would allow, so you can see that it would be very much faster than carrying out the same task manually.
VBA is not a complicated language; most of the statements you’ll see here are easily understandable with just a little experience. However, VBA can control a very large number of ‘objects’ and ‘methods’, and it’s the vocabulary and syntax around these that can take a long time to pick up. So please don’t expect to become a Word programming expert by the time you get to the end of this project.
If you work through this project and you begin to wonder if automation could help you with some of the long‑winded or repetitive processes you have to carry out at work, please don’t hesitate to get in touch with us for a chat about the possibilities. The answer is probably Yes.
Windows, not Mac
At Template Automation, we are based entirely on MS Windows machines. VBA is available for Macs, but we have found that the environment in which you use it is not at all easy or intuitive. So this blog will not make any further reference to Macs.
What we’re going to do
This post is going to describe how to create a dialog which will present a list of Autotext entries (see What is Autotext?). The dialog will enable you to select and insert an entry into a Word document wherever the current insertion point is. The entries will be given special names so that only a specific set are listed.
We suggest a number of different Autotext entries for use when developing the dialog, but you’ll be able to redefine them once it’s all working so that you can insert content that is relevant to your particular interests.
Finally, we’ll show you how to add a button to the Quick Access Toolbar to launch the dialog.
Letting It All Happen
By default, Word doesn’t let you run VBA code (macros), so you have to change its settings first. If you’re building this project on a system at work, you might be advised to clear it with your IT Department first.
- Select File/Options. The Word Options dialog will open.
- Select Trust Center, then Trust Center Settings… The Trust Center dialog will open.
- Select Macro Settings and then select the option for Enable all macros:
- Click OK on this dialog and on the Word Options dialog to return to the Word window.
If you wish, at the end of each session on this project you can reset the Macro Settings option back to what it was before you started.
‘Autotext’ refers to blocks of text, graphics, tables, etc stored in the template file to which a document is attached. The examples we’ll use only contain one type of content, but there’s nothing to stop you creating an Autotext entry consisting of text and graphics, tables and graphics, text and tables and so on.
Autotext entries store the formatting of whatever is selected at the time the entry is created. So things like corporate colours, special layouts, non‑standard formatting and so on will be reproduced exactly as they appeared when the block was created. If you find that your corporate documents all look different because everyone sets the fill colours in table cells slightly differently, a standard table saved as Autotext will come in very useful.
A New Template for this Project
As you probably know, the Normal template is used by default for new documents. However, you might wish to create a new template for your work on this project as it will eventually store the Autotext entries and the VBA code for the dialog.
Word makes a distinction between templates which are not ‘macro-enabled’ and those which are. An ordinary, non-macro-enabled template will have the file extension:
– while a macro-enabled template will have the file extension:
- Open a new document.
- Select File/Save As…
- Select a convenient folder to store the template in.
- Give the file a descriptive name.
- Choose the Word Macro-Enabled Template (*.dotm) file type:
- Click Save.
Note:An ordinary template (.dotx) can store Autotext, but will not allow you to store VBA code.
Adding Autotext to the template
As we mentioned earlier, the Autotext entries for this project are going to be given special names so that only the ones we are interested in appear in the dialog. Our names will begin with:
– as this string of characters is unlikely to appear in normal use.
To make the entries’ names more user-friendly, we’ll strip off the AT_ before they are displayed.
Example Autotext Entries
Re-create the following items in Word. We’ll discuss how to save them as Autotext shortly (see What is Autotext?).
Some Blocks of Text
Video provides a powerful way to help you prove your point. When you click Online Video, you can paste in the embed code for the video you want to add. You can also type a keyword to search online for the video that best fits your document. To make your document look professionally produced, Word provides header, footer, cover page, and text box designs that complement each other. For example, you can add a matching cover page, header, and sidebar.
Click Insert and then choose the elements you want from the different galleries. Themes and styles also help keep your document coordinated. When you click Design and choose a new Theme, the pictures, charts, and SmartArt graphics change to match your new theme. When you apply styles, your headings change to match the new theme. Save time in Word with new buttons that show up where you need them.
To change the way a picture fits in your document, click it and a button for layout options appears next to it. When you work on a table, click where you want to add a row or a column, and then click the plus sign. Reading is easier, too, in the new Reading view. You can collapse parts of the document and focus on the text you want. If you need to stop reading before you reach the end, Word remembers where you left off – even on another device.
Saving Autotext Entries
- Select the content you wish to save as an Autotext entry.
- Select the Insert tab.
- Click the Quick Parts button. The appearance of this button will depend on how your ribbon is displayed:
- Select Autotext from the drop‑down menu. A window showing the current entries will open.
- Click on Save Selection to Auto Text Gallery:
- Enter your Autotext entry’s name
- Check that Gallery is set to AutoText
- Check that Category is set to General
- Add a Description if you want to
- Check that Save in is set to the name of your template
- Check that Options is set to Insert content only
- Click OK to store the new entry.
- You might choose to save the template after creating each new entry.
Assuming that you have created the Autotext entries suggested in the Example Autotext Entries section, you should have nine entries (in addition to any already stored in the template):
Inserting Autotext Entries Into a Document Manually
As you added the Autotext entries, you will have seen them appear in the Autotext Gallery:
To insert an entry manually:
- Click in the document where you want the entry to be inserted.
- Click on the Quick Parts
- Select Autotext from the drop-down menu.
- Scroll down through the entries until you find the one you want and click on it.
The entry will appear at the insertion point.
Note: The entry will replace whatever is selected when you click on the Quick Parts button. Be careful.
Updating Autotext Entries
If you need to update an entry:
- Create the new content (or insert and edit an existing entry).
- Select the new/updated content.
- Follow the instructions in the Saving Autotext Entries section.
- You will be asked if you want to re-define the Autotext entry. Click Yes.
So Far (1)
So far, then, we have had a look at what sort of template you’ll need to create a Visual Basic for Applications (VBA) project. We have seen what Autotext is, and how you create, update and insert entries manually.
Now we take a look at what Word (and the other MS Office applications) offers us when automating applications.
Where to find the VBA IDE in Word
IDE stands for Integrated Development Environment.
“An integrated development environment (IDE) is a software application that provides comprehensive facilities to computer programmers for software development. An IDE normally consists of a source code editor, build automation tools, and a debugger.”
The keyboard shortcut to the IDE is Alt+F11. When you press this key combination (in MS Windows, remember) the IDE will open:
You might find that the Project Explorer and the Properties window are not displayed when you open the IDE: press Ctrl+r to display the Project Explorer and F4 to display the Properties window.
What the Knobs and Buttons Do
As we mentioned earlier, the IDE provides comprehensive facilities for software development. For the purposes of this project, we’ll limit our attention to just the ones we need.
The IDE has four toolbars:
We’ll only be using the Standard toolbar. You can switch the toolbars on and off by right‑clicking in the menu area and making your selection from the context menu, or via the options in View/Toolbars.
The Standard Toolbar
References are links to libraries of code which define the properties and methods of the various objects available in Word. Properties are things like colour, font, size; methods are things which can happen to an object, like delete, copy, insert, initialize.
Make sure that they are checked and then click OK.
Adding a Form
- Click on the Insert menu and select UserForm.
A new form will appear in the work area and a UserForm icon will appear in the Project Explorer.
By default, the form will be named UserForm1. We can change this by clicking on the form’s icon in the Project Explorer and editing the (Name) entry at the top of the Properties window. Change it now to frmAT. The frm indicates that this object is a form, and the AT indicates that it relates to our Autotext project. You can invent your own naming convention if you like, just as long as it is useful and consistent.
- Change the form’s Caption to AutoText Inserter.
- Set the Width to 160 and the Height to 200. Alternatively, you can click on the form itself and drag the side and bottom handles until it is the size you want.
Further down the Properties window you’ll find entries for the form’s Width and Height.
What Controls do we Need?
This is a simple dialog. We only need a listbox for displaying the names of the Autotext entries, a button for telling the code to insert the selected entry, and another button for cancelling the operation and closing the dialog if we change our minds.
If it doesn’t, click on the Toolbox icon in the Standard toolbar:
If you hover your mouse over the Toolbox buttons, you’ll see the names of the controls they insert into the form.
You can experiment with the Forecolor, Backcolor and Font properties of the controls and the form itself if you like.
Adding the Code for Each of the Controls
We need to write code to cover four events:
- To close the form if the Cancel button is clicked
- To fill the listbox with Autotext entry names when the form opens
- To enable the Insert button when a selection has been made from the listbox.
- To insert the selected Autotext if the Insert button is clicked.
Cancel Button Code
- Double‑click on the Cancel button. The form’s code module will open with the beginning and end statements for the Cancel button’s click event already in place:
- Click in the empty line between Private Sub btnCancel_Click() and End Sub.
- Press Tab once to indent the line.
- Type Unload Me:
- Press ctrl+s to save your work.
- Press F5 to run the code. The dialog will open:
- Click on the Cancel button. The dialog will close.
And that’s the first section of the dialog’s code done.
Filling the Listbox
If you go back to the form’s code module you’ll notice two drop‑down lists at the top. The one on the left lists the UserForm itself and all of the controls on the form; the one on the right lists all of the events that can happen to each of them. There are a surprising number of events that can be used, but we’re only going to use the Click events for our three controls.
The UserForm has an Initialize event which occurs when the form is loaded into memory before being displayed. This event is what we’ll use to load the listbox with the names of the Autotext entries.
- Click on the left‑hand drop‑down and select UserForm. The default event for the form is Click, but that’s not what we want. You can delete the Click event’s code if you wish.
- Click on the right‑hand drop‑down and select Initialize. A new Sub for the Initialize event will appear.
- Click in the empty line between Private Sub UserForm_Initialize() and End Sub and enter the following code:
- Press F5 to run the code. The dialog will open, showing the names of the Autotext entries which begin with our special AT_ string:
- Click the Cancel button to close the dialog.
- When the Insert button is clicked, check to see if an entry has been selected in the listbox. If not, pop-up a message, asking the user to make a selection, then stop.
- Disable the Insert button until the user has selected an entry.
- Back in the IDE, select the UserForm’s listbox.
- In the Properties window, set the Enabled property to False. (You can double‑click on it to change it if you wish.)
- In the UserForm’s code module, select lstAT from the left‑hand drop‑down and Click from the right‑hand drop‑down. A new sub called lstAT_Click() will appear in the code window.
- Click in below the Private Sub lstAT_Click() statement and enter this line of code:
- Press F5 to run the code. The dialog will open, and you should see that the Insert button is disabled. Clicking on it has no effect.
- Select a listbox entry. The Insert button will become enabled, although it has no code behind it yet.
- Click the Cancel button to close the dialog.
- In the IDE, double‑click on the Insert You’ll see that a new sub called btnInsert_Click() is added to the code window.
- Click in below the Private Sub btnInsert_Click() statement and enter the following code:
- Now would be a good time to save your work. Ctrl+S will do it.
- Go back to the Word document, and select an empty line.
- Go back to the IDE, select the UserForm and press F5. The dialog will appear in front of the document.
- Select an entry from the listbox and click OK.
- Go back to the Word document again. You should see that the chosen Autotext entry has been inserted at the insertion point.
- Select Insert/Module. A new module, named Module1, will appear in the Project Explorer. The module will be open, ready for code entry.
- Enter the following code:
- Save your work.
- Select File/Options. The Word Options dialog will open:
- Select Quick Access Toolbar.
- Select Macros from the left‑hand drop‑down.
- Select the name of your template from the right‑hand drop‑down.
- Select Module1.Launcher from the left‑hand listbox.
- Click the Add>> button. Project.Module1.Launcher will be copied into the right‑hand listbox.
- Select the new entry and click Modify… The Modify Button dialog will open:
- Select a suitable icon and change the display name to AT Launcher.
- Click OK to close the Modify Button dialog
- Click OK to close the Word Options dialog. Your chosen icon should appear in the QAT:
- Save your work.
- Click on the new icon in the QAT. Your dialog should appear.
- Close the dialog.
- Close the template.
- We looked at what sort of template you need to create a Visual Basic for Applications (VBA) project.
- We have seen what Autotext is, and how you create, update and insert entries manually.
- We have seen how to get access to the IDE and we have had a brief tour around the features of most relevance to this project. There are many other features, but you’ll have to investigate them for yourself.
- We have created a new UserForm and we have seen how to add controls to them and how to set the controls’ properties in the Properties window.
- We have written code to automate the Click events of the listbox and the two buttons. We have also added code to automate the form’s Initialize event – filling the listbox with processed Autotext entry names.
- We made sure that the Insert button cannot be clicked until an entry has been selected. Had we not done this, the system would have reported a Run‑time error when it tried to find the Autotext entry to insert.
- At each stage we tested the form to see if the new code was working as intended.
- Having built all the functionality we wanted into the form, we wrote a short macro to launch the form (Module1.Launcher).
- Finally, we added a QAT button that ran the Launcher macro then we modified the appearance and the name of the button.
So, what’s this code doing?
|Private Sub UserForm_Initialize()||This introduces the code which handles the Initialize event for this UserForm|
|Dim AT As AutoTextEntry, Entry_Name As String||Set memory space aside for two variables: AT is an AutoTextEntry, Entry_Name is a string of characters|
|For Each AT In ActiveDocument.AttachedTemplate.AutoTextEntries||Work through the collection of AutoTextEntries in the template attached to the active document, setting the AT variable equal to each in turn|
|If Left(AT.Name, 3) = “AT_” Then||If the first three characters in AT’s name are AT_, then carry out the instructions following, until you get to the End If statement|
|Entry_Name = Mid(AT.Name, 4)||Set the Entry_Name variable equal to AT’s name, beginning at the fourth character|
|Entry_Name = Replace(Entry_Name, “_”, ” “)||Replace any underscores in the Entry_Name variable with spaces|
|lstAT.AddItem Entry_Name||Add the final value of the Entry_Name variable to the lstAT listbox|
|End If||The closing statement of the If/Then statement|
|Next||Loop back and repeat the process with the next AutoText entry. Keep going until there are no more|
|End Sub||This is the end of the code which handles the Initialize event|
Note: If you are connected to the Internet, you should be able to click in a word in the code and press F1 for on-line Help on that word. Admittedly, sometimes the Help is a bit cryptic, and sometimes Help is no help at all. But try it with the Replace function and see what you think.
Enabling the Insert Button
When the dialog opens, nothing will be selected in the listbox. So it won’t be appropriate to run the code which inserts the autotext entry. There are (at least) two ways we can prevent this from happening:
Method 2 has the great advantage of simplicity, so that’s what we’ll go for.
When the user clicks on the lstAT listbox, the code here runs and enables the Insert button.
Inserting the Selected Autotext Entry
When the Autotext entry is inserted, it will overwrite anything which is currently selected. If that happened to be all of the text in your document, you would lose it all. So, before doing the insertion, we must ensure that nothing is selected. This where the Collapse method comes in; it collapses the selection down, either to the beginning of the selected content or to its end.
Note: The underscore at the end of the AttachedTemplate.AutoTextEntries… statement indicates that the statement continues on the next line.
Launching the Dialog from the Quick Access Toolbar
As it is not very convenient to have to go into the VBA IDE each time you want to launch the dialog, we can add a button to the Quick Access Toolbar (QAT) to simplify the process. The button we add will run some code which launches the dialog, so first we have to write the code. This time, the code will be in an ordinary code module, rather than in the module belonging to a form; this makes it accessible from outside the IDE. Back in the IDE, then:
Now that we have the code, we just have to connect it to a button on the QAT. In the Word window:
So Far (2)
So, what have we achieved after all this?
Find the template in your file system and double‑click it to open a new document. (Make sure that you’re not editing the template: a new document will have Document1 – Word in the title bar.)
Click the Launcher button on the QAT and try inserting some of the Autotext entries.
Once you have satisfied yourself that it’s all working as planned, have a think about the templates you usually use, and what you might usefully store as Autotext. As we mentioned earlier, you can store just about anything that you would normally find in a Word document.
While Word is generally very stable, it has been known to fall over irretrievably: files sometimes simply won’t open, macros get lost, and so on. One way of avoiding most of the frustration so caused is simply to keep saving the file you are working on as a new version. This doesn’t involve anything more than incrementing a version number or letter in the filename:
AT Template Project Va.dotm
– gets saved as
AT Template Project Vb.dotm
Admittedly, you end up with several only slightly different copies of the same file, but the old ones can be deleted when you no longer need them.
This same approach can be adopted when you are spending a lot of time and effort setting up styles and layouts in the template itself. If there’s the slightest chance of losing it all, make a new version and carry on from there. A good general rule is not to go longer without making a new version than you’d be prepared to spend recreating what you might have lost.
Automated Numbering in Autotext Entries
If one of your entries contains one or more numbered headings, and if your headings are automatically numbered – as they should be – Word will automatically adjust the headings’ numbers so that they fit in with the current heading numbering sequence in the document.
Editing Inserted Autotext
Content inserted via Autotext is fully editable: text can be formatted, deleted, amended; tables can be formatted and have the numbers of rows and columns altered by the usual methods; graphics can be manipulated and resized as necessary.
Links Between Word Templates and Documents
When you create a new document based on a Word template, Word sets up a link between them so that the document has access to the Autotext entries and the VBA code stored in the template. As you might expect, moving the document (or the template) to another file system – or even just to another location on the same system – can break the link and cause the entries and the code to become unavailable to the document.
Published on Wednesday, January 31st, 2018, under Uncategorized