Programming Publisher 2003 Made Easy: Lesson 3
Microsoft Office Publisher 2003
Summary: Learn how easy it is to use the Publisher object model to write macros that automate tasks in Publisher and save you time and energy. This lesson covers how to create custom dialog boxes, and how to use conditional logic and data variables to make your macros even more flexible and useful. (14 printed pages)
In our last lesson, we discussed the basics of object-oriented programming. In this lesson, we build on that by taking a close look at two special types of objects: forms and controls. Used together, forms and controls enable you to create a user interface for your macro, for example, dialog boxes. Then we introduce two new programming concepts:
- Conditional logic, which you can use to control what code Publisher runs under what conditions.
- Data variables, which you can use to store and use data in your macros, much like object variables let you store and use objects.
Finally, we pull it all together and show how you can combine these techniques to create robust, flexible macros that do what you want, when you want.
Let's switch gears for a moment and focus on one of the coolest aspects of Microsoft Visual Basic for Applications (VBA)—the ability to create your own custom dialog boxes. These are called forms in VBA. You can create and design your own forms, and include things such as buttons, check boxes, or text boxes on them. You can then include code in your macros to display your forms.
Terminology The buttons, check boxes, and other elements you can add to a form are generically called controls.
Sure, forms look cool, but what's the advantage to including them in your macros? Forms provide a way for your macro to communicate back and forth with the user while it runs. Dialog boxes are great ways to display information to the user, and to gather information from users. By doing this, you can make your macros much more flexible, and therefore, much more powerful and useful. For example, you might want to show a progress dialog box while your macro is running, or allow users to specify specific options before your macro does its magic.
Terminology The windows, dialog boxes, and other on-screen elements of an application like Publisher are collectively referred to as the application's user interface. This is because they enable interaction between the user and the application code itself.
To demonstrate this, let's take another look at the CreateNewsletter macro we've been working with for the last few lessons. In the last lesson, we created an event handler procedure so that the CreateNewsletter macro automatically ran every time you opened the publication you saved it in. But that's not very realistic, is it? What if you don't want a new newsletter every time you open the file? Wouldn't it be nice to present the user with the option of not running the macro?
Glad you agree, because that's our next exercise. We're going to create a form that asks the user if they want to create a new newsletter, and then write code for Publisher to display our form when the publication is opened.
To create the dialog box
- Open the publication that contains the macro in Publisher, and then from the Tools menu, select Macro and click Visual Basic Editor to open the Microsoft Visual Basic Editor.
- In the Project Explorer window, right-click on the Microsoft Office Publisher Object folder, select Insert, and then click on UserForm.
Tip If any of the windows you want are not visible, you can display them by clicking on their name on the View menu.
The Visual Basic Editor adds a new, blank form to your project. Notice that a 'Forms' folder is added in the Project Explorer window.
Figure 1. A new form
- Click on the form name in the Project Explorer window to make sure that it's selected. When the form is selected, the Visual Basic Editor displays the properties of the form in the Properties window.
- Change the name and caption of the form. In the Properties window, change the (Name) property from "UserForm1" to "frmNewsletter". Next, change the Caption property from "UserForm1" to "Create Newsletter?".
- Add the controls you want to the form. When you click on the form itself in the Code window, the Toolbox appears. The Toolbox contains controls, such as buttons, text labels, and check boxes, that you can put on your form. To put a control on your form, drag-and-drop the control from the Toolbox to the form. You can then position and resize it as necessary.
Tip To find out the name of a control type, hover over the control icon in the Toolbox. The Visual Basic Editor displays the name of the control as a tool tip.
For our dialog box, we want to ask the user whether they want to create a newsletter. So we want to give the user a choice between those two options, and some explanatory text. To do this, we'll need a label for the explanatory text, and two buttons, one for each option.
- Drag-and drop one Label control and two Button controls onto the form.
- Customize the controls as necessary by changing their properties. When you select a control, the Visual Basic Editor displays its properties in the Properties window.
- Click the Label control to select it. Its properties display in the Properties window. Change the Caption property to "Do you want to create a newsletter?".
- Next, click one of the Button controls to select it. Change the (Name) property to cmdYes, and the Caption property to "Yes".
- Now click the other Button control to select it. Change the (Name) property to cmdNo, and the Caption property to "No".
- Position the controls on the form as you want.
Tip The grid of dots on the form is there to help you position your controls. The dots don't appear on the dialog box when it runs.
Your finished dialog box should look something like this:
Figure 2. The CreateNewsletter Dialog Box
Programming Your Dialog Boxes
So now we have a great-looking user interface that does. . .nothing. But don't fear; we're about to change that. It's time to write the code that makes your form work.
Remember how, in the last lesson, we discussed how objects have properties, methods, and events? Well, forms and the controls you put on them are objects as well. This means that forms and controls have properties, methods, and events as well. In fact, you've been working with the properties of forms and controls already, only you were changing them in the Properties window, rather than by using code.
Now we're going to use the methods and events of forms and control to make the frmCreateNewsletter form do what we want.
The first thing we want the form to do is to automatically appear when the user opens the publication. So now we need to modify the Document_Open event handler procedure we wrote in the last lesson.
To have Publisher display the dialog box when the user opens the file
- In the Project Explorer window, double-click the ThisDocument object. This opens the ThisDocument Code window.
- Find the Document_Open event handler procedure. It should look like this:
Private Sub Document_Open() CreateNewsletter End Sub
- Change the code so it reads:
Private Sub Document_Open() frmNewsletter.Show End Sub
Our form, frmNewsletter, is an object, and Show is one of its methods.
- Save the publication.
Now when the user opens the publication, the Document_Open event handler calls the frmNewsletter.Show method, which displays our dialog box. But it still doesn't do anything but sit there and look pretty. We want it to run the CreateNewsletter macro if the user clicks the cmdYes button, and to just go away if the user clicks the cmdNo button. So what should we do?
The buttons are controls, and controls are objects. Objects can have events. And, in fact, button controls have Click events. So we can write event handler procedures for each button's Click event.
To write a click event handler for a control
- In the Project Explorer window, double-click the frmCreateNewsletter form icon, to make sure it's selected.
- Double-click the cmdYes button. This opens the Code window, and automatically creates an empty cmdYes_Click event handler procedure:
Private Sub cmdYes_Click() End Sub
- Type two lines of code into the empty event handler procedure, so it looks like this:
The first new code statement calls the CreateNewsletter macro, which creates the new newsletter. The second code statement calls the frmNewsletter form's Hide method, which removes the form from the screen.
- Do the same for the cmdNo button. In the Project Explorer window, double-click the frmCreateNewsletter form icon, then double-click the cmdNo button. Add code in the empty cmdNo_Click event handler procedure so that it looks like this:
Private Sub cmdNo_Click() frmNewsletter.Hide End Sub
Again, this single line of code removes the dialog box from view if the user clicks the cmdNo button.
- Save the publication.
Close the publication and re-open it to see the dialog box in action.
There it is. You now know the basics of designing and running forms in VBA. But, to give your forms the flexibility they need to become really powerful, you should learn a few simple programming concepts. So let's talk about those:
- How to program your macro to do different things under different conditions.
- How to store and use information in your macros.
Once we address these simple concepts, we'll show how you can combine them with forms to create powerful, responsive macros that really make your life easier.
Conditional Logic: If This, Then That
Sometimes you want your macro to do different things, based on the state of things. You can use an If. . .Then control statement to have your macro run different code based on a specific condition. An If. . .Then statement has this basic pattern:
Terminology Control statements refer to code statements in a procedure that control what code in the procedure runs, and in what order.
The TestCondition is something that can be true or false. If the condition is true, the macro runs the code in the statement. If it's false, the macro doesn't run the code. The following table lists some conditions that can be true or false:
Table 1. Conditions and results
|Document.Name = "SpringSaleFlyer"||True if the publication's name is SpringSaleFlyer.|
|Document.Pages.Count > 5||True if the publication has more than five pages.|
|numItems < > 10||True if the numItems variable does not equal 10.|
Consider the following fake code example:
If Outside.Weather = Sunny Then Me.GoFishing End If
According to this control statement, if it's sunny, I'm going fishing.
That's the simplest form of the If. . .Then control statement. It's good for controlling whether or not you want Publisher to run a specific set of code statements. What it doesn't specify is this: what do I do if it's not sunny? For that, we have to add an additional, optional control statement to the If. . .Then statement. We need an Else statement. Use the Else statement to specify what code the macro runs if the test condition is false. Here's the structure for an If. . .Then statement with an Else statement:
So to expand on our earlier example:
If Outside.Weather = Sunny Then Me.GoFishing Else Me.GoWork End If
According to this new statement, if it's sunny, I go fishing. If the weather is anything but sunny, I go to work. This kind of statement is good for either/or situations, where there are only two outcomes. You want the macro to run this set of code statements, or that set of code statements.
So far, that's all pretty straight-forward. But what if you wanted more than two possible outcomes? What if you want three, or four, or more? Then you have to add one or more Elseif statements to your If. . .Then control statement. This gets a little tricky. For each Elseif statement, you include a condition that's true or false. The macro only looks at your Elseif statement if the initial If statement is false. Then, if the Elseif statement is true, the macro runs the code in the Elseif statement.
If FirstTestCondition Then Code statements Elseif SecondTestCondition Then Code statements Else Code statements End If
This is probably best explained with another example:
If Outside.Weather = Sunny Then Me.GoFishing Elseif Outside.Weather = Snowing Then Me.StayHome Else Me.GoWork End If
If the weather is sunny, I go fishing. If the weather isn't sunny, but it's snowing, then I stay home. If the weather is anything else besides sunny or snowing, I'm going to work.
Storing and Using Data in Your Macro
Sometimes it's helpful to create a variable to hold a piece of information, like a sentence or a number. These pieces of information are called data, rather than objects. Data is just information; it doesn't have properties, methods, or events like objects do. But the way you create and use data variables is pretty close to how you create object variables.
To create and use a data variable
- Declare the variable name, and define what type of data you want to store in it, using the following pattern:
Dim variableName As dataType
You still have to write a
Dimstatement for the data. Only here, you specify the data type, not the object type, like in the following two examples:
Dim strMySentence as String Dim intMyNumber as Integer
As you probably guessed, String and Integer are data types, just like Page and Shape are object types. The data type determines what kind of information your data is. For example, String data is a string of characters, like a word or sentence. Integer data is a whole number, like 5 or 83701.
- Set the contents of the data variable.
Once you have your data variable, you can set the information it contains. Again, declaring the variable reserves the space in computer memory and the assignment statement fills the space with the specific information. Only when setting a data variable, we don't use the
Setkeyword. You just use the equal sign:
strMySentence = "Buffy, where's the cheese?" intMyNumber = 34590
- Use the variable name in your code whenever you want to refer to that data.
So here it is, all together:
Dim strMySentence as String strMySentence = "Buffy, where's the cheese?" myShape.TextFrame.TextRange.Text = strMySentence
To find out more about data, including a list of the data types you can use in Visual Basic, see Fundamental Variable Data Types.
Note that you can use a data variable in your code wherever you could use that type of data. In the previous example, this code statement:
myShape.TextFrame.TextRange.Text = strMySentence
Is the same as writing this code statement:
myShape.TextFrame.TextRange.Text = "Buffy, where's the cheese?"
Now let's put it all together.
Suppose you want some options when you create your newsletter. For example, say you change the newsletter's color scheme based on the season. Sometimes you include a calendar in your newsletter, sometimes you don't. Wouldn't it be great if you could specify those options, and then have Publisher create the newsletter for you?
We've already got the frmCreateNewsletter form, which lets the user choose whether or not to create the newsletter. Let's add another form to let the user choose the options they want on their newsletter.
So here's what we want to happen: When the user opens the publication, the Create Newsletter form appears. If you click Yes, the new dialog box with the newsletter options appears. You can then select the options you want, and click OK. Clicking OK creates the newsletter with the options you select.
To do this, we're going to use everything we learned in this lesson.
Creating the newsletter options dialog box
First, let's create the Newsletter Options dialog box.
- In the Visual Basic Editor, right-click the Forms folder, select Insert and then click UserForm. This inserts a new, blank form into the project.
- Drag the following controls from the Toolbox and drop them on the form:
- one Frame control
- four OptionButton controls
- one CheckBox control
- two CommandButton controls
Important Drop the Frame control onto the form, and then drop the four OptionButton controls onto the Frame control. That way, Visual Basic knows you want to use the option buttons as a group. When option buttons work as a group, you can only select one option button at a time. For example, you can't have both the Spring and Summer option buttons selected.
- Select each control, and change its Name and Caption properties to what's listed in the follow table.
Table 2. Custom control names and captions
Control (Name) Property Caption Newsletter Options form frmNLOptions Newsletter Options Spring radio button rbSpring Spring Summer radio button rbSummer Summer Fall radio button rbFall Fall Winter radio button rbWinter Winter Calendar check box chkCalendar Include a Calendar OK button btnOK OK Cancel button btnCancel Cancel
- Arrange and resize the controls and form so that the finished product looks like this:
Figure 3. The finished newsletter options dialog box
Writing code to make the newsletter options dialog box work
Next, we need to write the code that creates the newsletter when you click the OK button.
- In the Project Explorer window, click the frmNLOptions form icon, to select the frmNLOptions form. Then double-click the OK button. The Visual Basic Editor creates an empty btnOK_Click event handler procedure:
Private Sub btnOK_Click() End Sub
- In the btnOK_Click event handler, type the following code:
Dim strScheme As String Dim newDoc As Document If rbSpring.Value = True Then strScheme = "Meadow" ElseIf rbSummer.Value = True Then strScheme = "Green" ElseIf rbFall.Value = True Then strScheme = "Desert" ElseIf rbWinter.Value = True Then strScheme = "Cavern" End If Set newDoc = NewDocument(pbWizardNewsletters, 52) newDoc.ColorScheme = newDoc.Application.ColorSchemes(strScheme) If chkCalendar.Value = True Then newDoc.Pages.AddWizardPage 2, pbWizardPageTypeNewsletterCalendar End If frmNLOptions.Hide
Let's take a closer look at this code, so we know exactly what it's doing.
First, we create two variables:
Dim strScheme As String Dim newDoc As Document
- A data variable, to hold the name of the color scheme you choose
- An object variable, to hold the Document object that represents the new publication we're going to create
Then, we write an If. . .Then statement that fills the strScheme variable with the name of the color scheme you choose:
If rbSpring.Value = True Then strScheme = "Meadow" ElseIf rbSummer.Value = True Then strScheme = "Green" ElseIf rbFall.Value = True Then strScheme = "Desert" ElseIf rbWinter.Value = True Then strScheme = "Cavern" End If
Here's how this If. . .Then statement works. Each option button has a Value property. The Value property is True if the button is selected, False if it is not. Each If or Elseif statement tests to see if a particular button is selected. If that particular button is selected, then the macro fills the strScheme data variable with the name of a specific color scheme: Meadow for spring, Green for summer, Desert for fall, and Cavern for winter.
A plain English way of writing this If. . .Then statement would look something like this:
If the Spring button is selected, then the color scheme is "Meadow".
Or else, if the Summer button is selected, then the color scheme is "Green".
Or else, if the Fall button is selected, then the color scheme is "Desert".
Or else, if the Winter button is selected, then the color scheme is "Cavern".
Once we have the color scheme name, we go ahead and create the newsletter:
Set newDoc = NewDocument(pbWizardNewsletters, 52)
And then we change the newsletter's color scheme to the chosen color scheme. Remember, the name of the chosen color scheme is stored in the strScheme variable, so using it is the same as using the scheme name itself:
newDoc.ColorScheme = newDoc.Application.ColorSchemes(strScheme)
The chkCalendar check box control also has a Value property, which is True if the control is checked. We use a basic If. . .Then statement to test if the Value property is True. If it is, the macro runs the code inside the statement. This code adds a new page containing a calendar to the publication.
If chkCalendar.Value = True Then newDoc.Pages.AddWizardPage 2, pbWizardPageTypeNewsletterCalendar End If
Lastly, we hide the frmNLOptions form, because we're finished with it.
Hooking Up the Newsletter Options Dialog Box
To finish up, we need to change what happens when you click Yes on the Create Newsletter form. This means we need to change the cmdYes_Click event handler procedure. Right now, it calls the CreateNewsletter macro:
But we want it to display the Newsletter Options dialog box, so change the code to read:
Private Sub cmdYes_Click() frmNewsletter.Hide frmNLOptions.Show End Sub
Remember, the frmNLOptions form is an object, and Show is one of its methods.
Now save your publication, and close it. Open the publication and take your newsletter project for a test drive. You did it!
Forms are a great way to display information to the user, and to gather information from users while your macro runs.
The buttons, check boxes, and other elements you can add to a form are generically called controls.
Forms and controls are objects, which means forms and controls can have properties, methods, and events. You can use these in your macro code.
Use an If. . .Then control statement to have your macro run different code based on a specific condition. An If. . .Then statement has this basic pattern:
Add an Else statement to specify what code the macro runs if the test condition is false. Here's the structure for an If. . .Then statement with an Else statement:
Add one or more Elseif statement if you want more than two outcomes. The macro only looks at your Elseif statement if the initial If statement is false. Then, if the Elseif statement is true, the macro runs the code in the Elseif statement.
If FirstTestCondition Then Code statements Elseif SecondTestCondition Then Code statements Else Code statements End If
You can create a variable to hold information, or data, just like you can create an object variable to hold an object. To create a data variable:
- Declare the variable name, and define what type of data you want to store in it:
Dim variableName As dataType Dim strMySentence as String
- Set the contents of the data variable, using the equal sign:
strMySentence = "Are we done yet?"
- Use the data variable name in your code to refer to that data.
If you completed these three lessons, you now have a toolbox full of techniques for using the Publisher object model to create macros that save you time and money. Congratulations!
Special thanks go out to Frank Rice, for allowing me to adapt the structure of his Super-Easy Guide to the Microsoft Office Excel 2003 Object Model article for this series of lessons. Thank you, Frank.
This section lists a number of resources you can use to learn more about the products and technologies mentioned or used in this article.
- Programming Publisher 2003 Made Easy: Lesson 1
- Programming Publisher 2003 Made Easy: Lesson 2
- Programming Publisher 2003 Made Easy: Lesson 4
- Programming Publisher 2003 Made Easy: Lesson 5