Reader Autumn recently asked a question on an older post regarding office-wide letterheads:
What if you have multiple offices and want to do a drop down picklist of what office they would like to choose? Is that an easy process? For example we have 13 offices and want to only change one of our letterheads if someone gets fired/hired.
Can it be done. Yes.
Is it an easy process? It took me about half an hour to develop and test a solution from scratch. It is going to be a little more in depth than other projects on this blog, and, to be honest, if you're not familiar with the VBA editor for Microsoft Word, it might seem a bit daunting. But let's try to walk through one potential solution.
To start with, create all of your letterheads in separate .dotm files. The file name should be the location of the office. So, for example, if I had offices in Columbus, Cleveland, and Chillicothe, I should have three separate letterhead files called Columbus.dotm, Cleveland.dotm, and Chillicothe.dotm.
Next, save them all in the same folder and location. This step is very important.
Then, we're going to create a new document called letterhead.dotm. It should be a blank document, nothing on it. Here is where it is going to start getting a bit tricky because you're going to have to do some work in the VBA code editor. To open it, hit Alt + f11.
When it opens, in left pane near the top will be a list of files. One of them should be called "letterhead.dotm". Right click on it, choose insert, and then choose UserForm.
To the UserForm (which should be called UserForm1), you will add three items. The first is a label, which you'll change to read "Which Letterhead?" The second is a ComboBox. The third is a button that you'll change to read "Ok". When you're done it should look like this (I've identified in the toolbox with arrows which tool let's you add each item).
Next, you need to right click in the middle of your form (though not on any of the things you added) and choose "View Code". Make sure that the left dropdown box in the new window says UserForm and change the right box to Initialize.
Underneath where it says Private Sub UserForm_Initialize() you are going to add the following code:
It is important that each of the city names be spelled exactly as you named the files earlier on. If you're like Autumn and have 13 offices, you would just keep repeating the .AddItem "cityname" until you had them all listed. Note, if you changed the name of your combobox from earlier on from combobox1 to something else, you will need to change it in the code above.
Next, you need to tell the computer what to do when someone hits the OK button. So, in the top left dropdown you will change it to the name of your button (probably CommandButton1) and you will change the right drop down to Click.
In the new area under Private Sub CommandButton1_Click() you're going to add the following code:
Dim selection As String
Dim FileName As String
selection = ComboBox1.Value
FileName = "path of your files" & selection & ".dotm"
You will need to change the red letters above to be the path of your letterhead files. For example, if you saved them on the c: drive in a folder called letterheads, you would change the red lettering to
It is important that you not forget that final slash.
So what does this do? It tells the computer to take the name of the city chosen and save it as a variable called selection. Then, it hides the popup userform. Next it create a variable called FileName that will be the path and name of the file you want opened. It closes the current document and opens your letterhead template.
The final step is to tell the template that when it starts up that it should show your combobox. To do this, right click on the file named "ThisDocument" under your Template Project called Letterhead and choose view code:
When the code window pops up, change the left drop down to Document and the right to New.
In the space under Private Sub Document_New(), add the following line:
Now, save everything, and you are finished. When you double click on your letterhead.dotm template, it will now open the blank document and show your pop up window letting you select your office. When you choose your office and hit OK it closes the current document and opens the appropriate letterhead. Best of all, if you place all of these files on a shared server, anyone in the office or company can use them.
Hope this answers your questions, Autumn.