Tell the truth: What was your initial reaction the first time you saw that animated paper clip, Clippit, on your screen? I couldn’t wait to find the option to turn him—uh, her—um, it off. (What gender is a paper clip, anyway?)

Two things have since changed my opinion about the Office Assistant feature. First, I found a character I could stand—the Einstein-like Office Assistant called The Genius. His dumpy, easy-going approach to life in Word-world was just the right accompaniment to my work.

The second thing that made me appreciate the various Office Assistants was my discovery that I could corral their services to provide custom help for my Office applications. Programming custom help is as easy as creating a message box, yet it provides much more functionality.

In this Daily Drill Down, I’ll show you how to program custom help into the Office Assistant feature and how to use the information returned when a user interacts with your custom assistant. By the time you’ve finished reading, you’ll be able to include custom help for your Office applications in just a few lines of code, without even knowing how to write a help file. Who knows, maybe you’ll even start to like that frenetic paper clip! (Then again, maybe not.)

About Visual Basic
You’ll be programming your custom help information in Visual Basic. To fire up the VBA editor in Office 2000 and Office 97, press [Alt][F11] from any Office application. In Office 2000, you can also choose Tools | Macro | Visual Basic Editor. In Office 97, choose Tools | Macro, then either Edit An Existing Macro or Create A New Macro.

The help information you program will appear with a user’s preexisting, installed Office Assistant. You’ll store your VB code in a module or a form that will be included with the Excel, Word, or other Office document that you’ll share with your end users. End users will be able to get your custom help information by accessing a button on a custom form, a button on the document itself, or a menu choice.

The Assistant Object Model
To program assistants to talk to users, your first task is to understand the Assistant Object Model. Fortunately, this model is fairly straightforward. At the top is the Assistant object. Beneath it is the Balloon object. The Balloon object includes two collections of objects, or controls: BalloonCheckboxes, and BalloonLabels.

The Assistant object refers to the Assistant itself. Even though a number of different Office Assistants ship with Office, you can only activate one at a time. If you want to change Assistants in code, you have to specify the filename of the Office Assistant you want to change. All the Assistant files use a file extension of .acs. You can find these files in C:\Program Files\Microsoft Office\Office. I’ve listed the primary properties and methods of the Assistant objects in Table 1.
Using Visual Basic, you can create new Office Assistants. Doing so, however, is beyond the scope of this article. For more information, check out the Microsoft Agent page.

Table 1
On Whether or not the Office Assistant is enabled I(If this is set to False, your Assistant code has no effect. You can turn it on programmatically, though.)
Animation What movement the Office Assistant should use (There are over 40 different animations, all defined by constants, such as msoAnimationGreeting and msoAnimationLookLeft. See the same property for the Balloon, below, for more information.)
Visible If the user hides the Office Assistant (as opposed to disabling it), this is set to False.
FileName Returns or sets the filename of the current Office Assistant
Move A method that allows you to place the Office Assistant where you want it
These are the main properties and methods of the Assistant object.

An Assistant Balloon is the message that appears whenever the Office Assistant wants to communicate with the user. The Balloon object is what you use to programmatically control both the appearance and contents of the balloon. Table 2 lists its primary properties and methods.

Table 2
Animation This controls what movement the Office Assistant should use. If you set this property for the Assistant, the animation happens immediately if the Assistant is visible. If you set it for the Balloon, though, the animation only takes place when the Balloon is made visible.
BalloonType This controls what type of content is in the body of the balloon. It is set to one of three constants: msoBalloonTypeButtons, msoBalloonTypeBullets, or msoBalloonTypeNumbers.
Button This controls which set of buttons, if any, appear at the bottom of the balloon. It is similar to the Buttons parameter of a message box (not related to the Buttons balloon type, which puts buttons in the body of the balloon). It is set to such constants as msoButtonSetOKCancel or msoButtonSetYesNo.
Heading This contains the text to be shown at the top of the balloon.
Icon This sets the icon, if any, to be shown besides the heading. It is set to one of the following constants: msoIconAlert, msoIconAlertCritical, msoIconAlertInfo, msoIconAlertQuery, msoIconAlertWarning, msoIconNone, or msoIconTip
Mode This sets the mode of the balloon to msoModeAutoDown, msoModeModal, or msoModeModeless. The default is Modal, which means the user must dismiss the balloon before continuing to work in the application. AutoDown makes the balloon disappear if the user clicks anywhere on the screen. Modeless allows the user to keep working with the balloon on the screen. Modeless balloons are often used in wizards; I’ll cover modeless balloons in a future feature. The default setting is Modal, and that’s what we will write today.
Text This sets the content that will go right under the heading.
The above is a list of the properties and methods of the Balloon object.

How to blow up balloons
Now that you have the basics of the Assistant and Balloon objects, you’re almost ready to write some code. First, though, you’ll need to understand the parts of a balloon, which I’ve shown in the diagram in Figure A.

Figure A
Balloons are made up of a heading, some text, labels, and checkboxes.

The Heading area is at the top of the balloon and is typically set in boldface. It will contain an icon if you set the Icon property of the balloon. The Text area comes right under the heading.

The Label and Checkbox areas are a little more complex. Each balloon contains five labels in a collection and five checkboxes in a collection. They are invisible, though, until you assign a text string to them. If you don’t assign any text to either the Label or the Checkbox areas, your balloon will show just the Heading, Text, and Button areas.

You assign text by setting the Text property of the desired label or checkbox, using the index number of the label or checkbox, like this:
Label(2).Text = “Some sort of text here.”

If you try to set more than five labels or checkboxes, you will generate an error.

The BalloonType setting (Buttons, Numbers, or Bullets) determines what the labels will look like. If it’s set to Bullets, you’ll get bullets; if Numbers, you’ll get numbers as shown in Figure A. If it’s set to Buttons, however, you get clickable text buttons (essentially option buttons).

If either a button label or a button at the bottom is clicked, the balloon is dismissed immediately. If a checkbox is selected, though, the balloon remains on screen.

The balloon shown in Figure A was built using the following code:
With Assistant.NewBalloon
 .BalloonType = msoBalloonTypeNumbers
 .Button = msoButtonSetOK
 .Heading = “Hello There!”
 .Text = “To use this feature:”
 .Labels(1).Text = “Enter your name.”
 .Labels(2).Text = “Click on OK to run.”
 .Checkboxes(1).Text = “Skip this in the future.”
End With

Programming an Office Assistant
Now you’re ready to write some useful code. A cardinal rule for programmers is not to change the user’s settings without resetting them to their original values, so the first thing you need to do is figure out whether or not the user has the Office Assistant turned on and visible. You’ll want to stuff those two settings into Boolean variables that you can use later to restore the settings.
  Dim blnIsOn As Boolean
  Dim blnIsVisible As Boolean

  blnIsOn = Application.Assistant.On
  blnIsVisible = Application.Assistant.Visible

Next, turn on the Assistant, if it isn’t already on.
  If Not blnIsOn Then
   Application.Assistant.On = True
  End If

Now, move the Assistant somewhere on the screen, make it visible, and have it do an initial animation.
  With Assistant
   .Move 100, 100
   .Visible = True
   .Animation = msoAnimationLookDownLeft
  End With

Finally, pop up a simple balloon that just conveys a message.
  With Assistant.NewBalloon
   .Heading = “Welcome!”
   .Text = “This application will help you produce your” & _
   “ weekly expense report. If you need help, simply” & _
   “ click the question mark button on the subsequent screens.”
   .Button = msoButtonSetOK
  End With

Adding more functionality
The code above could have been handled with a message box. I noted, though, that balloons have more features than the standard message box, so let’s start adding some.

Let’s suppose you’re building a custom wizard that walks a user through pulling records out of the corporate data warehouse, putting the data in Excel, and creating a graph based on the data. You’ve put a Help command button on your main form, and when it is clicked, you want the balloon to appear as shown in Figure B.

Figure B
You can create help for a custom application, such as a wizard.

By now, you should be able to figure out how to create the balloon: Simply set the properties for the various elements and then call .Show, right? The problem is, once you’ve shown the balloon, how do you return the choices?

The answer is that the .Show method itself returns a value based on which button in the bottom button set the user clicked. If you were using an OK/Cancel combination or an Abort/Retry combination, then the return value of .Show might be useful. As it is, the only thing the Cancel button tells you is that the user doesn’t need any help right now. So, the first thing you will do is check to see if Cancel was clicked. If so, then simply Exit Sub.

Now, what about the buttons in the body of the balloon? The .Show method also returns a value if the user clicked one of the button labels. You can also test for that and take action accordingly.

Finally, what about the checkboxes? Those aren’t returned by .Show; instead, you have to test the .Checked property of each one to see if the user selected any of them before dismissing the balloon.

Here is the code to generate the balloon shown above and take action based on the user’s choices:
Sub GetHelp_Click()

With Assistant.NewBalloon
  .BalloonType = msoBalloonTypeButtons
  .Heading = “Help for the Data Warehouse Wizard”
  .Text = “What do you want to see”
  .Labels(1).Text = “Help for the entire wizard.”
  .Labels(2).Text = “Just show help for the following topics:”
  .Checkboxes(1).Text = “Getting data from the data warehouse.”
  .Checkboxes(2).Text = “Putting the data into Excel.”
  .Checkboxes(3).Text = “Creating a graph.”
  .Button = msoButtonSetCancel
  ‘ Show the balloon, and load our variables with the results.
  intShowResult = .Show
  blnGetData = .Checkboxes(1).Checked
  blnIntoEx = .Checkboxes(2).Checked
  blnDoGraph = .Checkboxes(3).Checked
  ‘ See if they clicked Cancel.
  If intShowResult = -2 Then
   Exit Sub
  End If
  ‘ See if they wanted help on everything or on only parts.
  If intShowResult = 1 Then
   Call AllHelp      ‘ some routine to show total help
  ElseIf intShowResult = 2 Then
   ‘ Call appropriate routines, one after another.
   If blnGetData Then Call GetDataHelp
   If blnIntoEx Then Call IntoExHelp
   If blnDoGraph Then Call DoGraphHelp
  End If
End With

End Sub

Formatting your balloons
There are a few more features that are simple to implement and can add some polish and pizzazz to your application. By default, all the text in the balloon is black. You can set it to a new color by adding a {cf xx} string to your text, where xx is a color code. Table 3 lists the colors you can use:

Table 3
System color number Color
0 Black
1 Dark red
2 Dark green
3 Dark yellow
4 Dark blue
5 Dark magenta
6 Dark cyan
7 Light gray
248 Medium gray
249 Red
250 Green
251 Yellow
252 Blue
253 Magenta
254 Cyan
255 White
You can set text colors using these numbers.

For example, if you want your heading to be dark blue, you would set .Heading to {cf 4} My Heading {cf 0}.To underline text, add {ul 1}; to turn off underlining, add {ul 0} to your text string.

If you want to show an icon in the heading of your balloon, you can use the.Icon setting. You can also add a bitmap to any of the text strings in the heading, text, label, or checkbox areas. Again, this is done using braces enclosing three parameters, as listed in Table 4.

Table 4
Type The type of graphic to be used, either bmp for bitmap
or wmf for Windows metafile
Location The resource ID or path of the file to be used
Sizing factor The width of the file (omitted for .bmp files)
Use these values to set icons and bitmaps in your balloon.

In this Daily Drill Down, I’ve presented an overview of programming a custom Office Assistant and designing balloons. In a future Daily Drill Down, I’ll cover more advanced topics, such as using modeless balloons that stay on screen while the user works with your application. The code for these balloons includes a callback property. Look in your Visual Basic help for callback, and you’ll find a really good example, if you want to work ahead.
To learn more about Visual Basic scripting, read these TechProGuild features:

The authors and editors have taken care in preparation of the content contained herein but make no expressed or implied warranty of any kind and assume no responsibility for errors or omissions. No liability is assumed for any damages. Always have a verified backup before making any changes.