In last month's issue of TechTrax, I showed you how to Create Your Own Letterhead and I promised to show you how to add some cool automation this month.
In this article, I'll show you how to add some VBA code to your template so users can easily click to add your logo to a letter, or just as easily remove that logo should they want to print the letter on preprinted letterhead. For those of you who break out in a sweat when you hear the term VBA...fear not. This will be totally painless. In fact, it'll probably be easier than creating the original template from the last lesson.
But, if you haven't yet created your own custom letterhead template, you'd better get busy because you'll need it for this lesson. I've created mine as you can see below.
I've added my logo and moved it to right alignment. I've added a shaded line above and then added a shaded area below with contact details. All this is contained within the header. Now I simply need to add this heading information to an AutoText entry. Then I'll record a macro that will record my keystrokes and/or mouse moves as I add and then remove the logo. A quick menu customization for the template to add the macros to a custom menu and bingo...it'll be done.
The first step is to Open your template.
Now don't confuse this with creating a new document, you actually need to open the file this time, because you'll need to work on the design elements. Remember, when you work with a template as a designer you need to open it by clicking File > Open and go find your template. Be sure it shows the template name along the top of Word's title bar. When you use a template as a user, that's when you will click File > New and hunt out your template. File > New creates a new document from the template. Since we need to edit the master content of the template, as well as add VBA code to the template, we need to work with it in it's raw form...as a template..
Once you've opened the template itself, you will need to save all the header information off to AutoText. To do this, click View > Header/Footer to move into that layer of the template. Press Ctrl + A to select all of the content while in the header area, as shown below.
While the heading content is selected, click Tools > AutoCorrect and click on the AutoText tab.
A vital step here that can cause confusion, if you don't do this correctly, is to make sure that you have clicked the drop down under Look in: and selected the name of your template. If you don't, the AutoText may default into the Normal.dot and you'll go nuts trying to figure out where it went and what you did wrong. See the image below for direction.
Give this entry a reasonable name such as InsertLogo or just Logo. Then click Add. Hit Ctrl + S to Save your template. All the information you had in your header is now safely tucked away within your template in the AutoText entries. However, it only saved a copy, so the original content will still be within the header. This is what we want, because we will now record a macro as we delete this information. After you close the AutoText dialog box, click and close the header area by either clicking on the Close button on the Header/Footer toolbar or you can simply double click anywhere in the document area to return to that layer.
Time to record a macro. To start your recording, you can click Tools > Macros > Record Macro or use the shortcut button at the bottom of the Word screen marked REC. Just double click it to open the same macro recording dialog box.
Give the macro a name. Know that you can only enter one word to name your macro. However, notice in the image below that I've called this macro RemoveLogo. Hey, that's two words! Yup. Developers get away with using more meaningful terms by using several words, but no spaces. This tricks Word into thinking that you've only used one word. And to help us see the words more easily, we capitalize the first letter of each word.
Again, the vital step here is to make sure you click the drop down to select your template. This is probably the number one mistake that newbies make when trying to add automation to a template. They record or create this great code, but they don't realize they added it to their Normal template by default. There they are all proud of this great job they did and they send their newly designed template off to their colleagues, or worse...the boss, to show it off. Only to discover that it doesn't work because all that great code is sitting in their default template back on their computer! Oops!
So make sure that you select the correct template name and watch that little bugger, because if you click it in a hurry, you can miss it and it'll jump back to All Documents.
When you're ready to go, click OK. Word is now making a record of what you're doing. Here are the careful steps you'll need to make.
- Click View > Header/Footer or hit Alt + V + H (to move into the header layer)
- Press Ctrl + A (to select all the header information)
- Press Delete (to remove the header information)
- Click on the Close button on the Header/Footer or hit Alt + V + H (to toggle the header/footer off)
- Click the Stop button on the Recorder or hit Alt + T + M + R (to stop the recorder)
Great! We removed the header.
Hey...wait a minute! Something isn't right here??? Look at the image of our stationery below and see if you can figure it out.
Humm...that date looks a bit high, doesn't it? We removed all the information that was taking up about 1.5 inches of space in the header and now there's nothing there to replace it! So our date has moved up into that area. Now if you attempt to print a letter in this format off to a preprinted letterhead, isn't that date going to end up within the header logo area?
Then why the heck didn't you tell me about this before I messed it up!?!?!?!
<hee, hee> Ahh...I just like to see you work! Actually, that's sort of true. If I told you about this, you would not have had the chance to think it out yourself. And part of learning how to use computers is learning how to solve problems. So how are we going to solve this problem?
What if we just stuffed something else in there to take up that space? Like a blank spacer or just a bunch of returns?
Yup, that's exactly what you can and should do. Although we don't want anything to display in that area, we still need something in there to keep that same amount of space open in the header. When I create custom, automated templates for clients, I'll create a blank spacer image that I can drop into that spot that will be the same dimensions as the logo itself. But we can be lazy here and just hit return a few times. However, you should measure how much space was originally taken up by your logo and then make sure you add that same amount of space with returns or a blank image.
Go back, reopen the header area and do something to hold that same space in reserve. In my sample image below, I've hit return enough times to open the same space.
And since you'll want to have these returns saved to use later, we need to also save them to AutoText. So press Ctrl + A to select all those markers, reopen AutoText. Make sure you have the correct template selected and add this content. Give this a name such as BlankLogo or LogoSpacer. Click Add and close this dialog.
We now need to record a second macro that will read the logo into the letterhead. Go back into the macro recorder mode by clicking Tools > Macro > Record Macro or double clicking the REC button. This time give the macro the name, such as InsertLogo.
The steps you'll do here are:
- Move into the header/footer area (do you remember how?)
- Click Tools > AutoCorrect and click the AutoText tab
- Click on your Logo AutoText to select it
- Click Insert
- Close the AutoText dialog box
- Close the header/footer layer
- Stop the recorder
You now have two macros. But wait! Yes, one inserts the logo, but the RemoveLogo macro only deleted the information!
And yes, now we have to rerecord that RemoveLogo macro to actually replace the logo with the spacer we created. Hey...consider it practice!<smirk>
Go back into the macro recorder and give it the same name as you did the first time, which is probably RemoveMacro. Be sure that you check to make sure you have the correct template selected.
A tip here is that...you know you're going to need to overwrite a previous macro with the same name in the same template, right? So if you are not asked to confirm that you do want to overwrite the previous macro...you'll know you messed up somewhere! You either typed the name wrong or didn't properly select the correct template. If you did it right, you'll be asked to confirm the replacement. Say Yes.
Follow the same steps as you did the first time to move into the header and select all the content. However, rather than hitting Delete, this time you can either hit delete and then add in the spacer logo or, as long as you have the contents selected, you can just insert the spacer and it'll replace the currently selected content, thereby deleting the old and inserting the new. Either way, the end result should be that your spacer is now in the header.
Remember to close the AutoText dialog box and the header/footer layer before you stop the recorder.
Great! Just to show you what all that recording did, let's take a peak at the code.
Hit Alt + F11 to open the Visual Basic Editor (VBE). Your normal template is always the default project listed in the upper/left window. But any other documents you have open will also show up. Double click on the NewMacros code module that is listed as part of your stationery project in the Project Window, as shown below. This will open the code module in the code window and you can see what you've just recorded.
You may or may not have the first command: Option Explicit. This is important once you start writing your own code, because it forces you to write the code more professionally. But if you're not at that level yet, don't worry about it. The rest of your code should look pretty much the same. However, if it doesn't look exactly like my image above, not to worry. You may have moved around a little more and added some excess code. As long as it doesn't trash the end results, you don't really need to worry about it. However, if you can understand and see what you did wrong, you should modify the code or even record it over, properly, for efficiency sake.
Note that, although you simply recorded a macro, the results are VBA code. Word just wrote it for you. But this is the same stuff developers learn to read, understand (most of the time<grin>) and write to do some way cool automation. To see samples of what you can do when you give your soul over to VBA, see my Solutions page.
One last step. We need to add the macros to a custom menu so your users can easily click to add/remove the logo when they're using this template.
I'll run through the steps quickly. But if you're not familiar with this process, you might want to read my more detailed article here: Customized Toolbars and Menus.
To create a custom menu for your letterhead template, click Tools > Customize to move into that mode. On the Commands tab, move down and select the New Menu category. In the Commands window, grab and drag a New Menu command up to your Menu Bar in Word and release.
You can now right click to change the name. Add an ampersand in front of the letter you'd like to be your hotkey. Then you can access this menu by hitting Alt and the hotkey character.
If you want this menu set-off from the others, you can create it as a new Group by clicking that option further down on the same menu you used to rename your menu.
Once the menu is created, you need to add the two macros to the menu.
Note that this is one of those places where the devs at Microsoft were smoking something funny when they designed this dialog box! The display needs to show the Project.CodeModule.MacroName. And since yours are most likely all still named with the default names, you'll see something like Project.NewMacros.MacroName, where MacroName is the item you need. However, if you customized the name, you may have a heck of a time trying to see the darn macro name! Half the time, developers who customize their projects with better names, because they may have a dozen code modules in a single project, have to play a little guessing game to try to grab the right macro because the window isn't wide enough to display all that information. Keep this in mind when customizing the names. Keep them short or you'll never find your macros in this window! (Granted, most devs will create a dynamic menu with code. But for quick, manual jobs, this can be a major frustration if your project names are not kept short!)
And remember...watch that template name at the bottom of this dialog box, too. If it doesn't say the correct name of your template, you'll be hitting a support group trying to find out why your menu doesn't display when you ship off your template!
As with the main menu, you can right click and rename these macros to something more reasonable and user friendly, since the default is the full project name, module and macro name.
There you have it! A cute, little, custom menu that will allow you and your users to insert and remove the logo.
Now the most important part of the whole project...testing! Test both processes several times. And when you're sure you did it right, email the template to a friend (who won't snitch if you messed up) and test it more on their computer. Only after you've done all this testing should you feel confident that you can pass it around the office and to the boss (or stuff it in a shared network drive where your users can all access it)!
You should be able to click File > New in Word, locate your stationery template, see the custom menu, click to remove the logo and then click again to reinsert it. If it doesn't work...reread the article!
A final note about this project...if you are also adding a footer, you can add the footer removal steps into the same header removal macro. Just use the Switch to Footer button on the header/footer toolbar to move into that area. Of course, you'll also need to save off the footer info as a separate AutoText entry and add the steps to insert that in the footer, too.
If you want to learn how to automate more documents, be sure to check out all my free AutoForm articles here: http://www.mousetrax.com/techpage.html#autoform. And if you want to learn, in short order, how to write VBA code, my popular Word AutoForms and Beginning VBA course will have you up and running, quickly. Don't believe me? See what many of my former students have said!
Have fun creating your custom, automated letterhead!
Dian Chapman is a 10 year Microsoft MVP, Technical Consultant and certified Microsoft Office Specialist, who specializes in document automation and database connectivity using Microsoft Office. To learn more about document automation, be sure to check out her many free tutorials on forms . To see some of her time/money saving automation solutions, visit Dian's Solutions page. And if you've always wanted to make your business more efficient, as well as save time and money by incorporating sophisticated document automation, be sure to hit Dian's consulting page...tell her your ideas and request a project quote.