Chapter 39: An Alternative to Message Boxes

Overview

In Chapter 5, I discussed using message boxes to interface with the user as a professional way to produce simple messages. However, if you want a more interesting and amusing way of displaying simple information and options, you can program Excel's Office Assistant to display a number of different graphics and balloon messages, and you can program your own options into his balloon that the user can choose from!

For those of you who do not know the Office Assistant, this is the animated character who appears when you request Help. It normally manifests itself as a paper clip with eyes, although other animated characters are available. You either love the Office Assistant or you hate it.

The Office Assistant operates in a similar way to a simple message box. Unfortunately, you do have to have an animation of the Office Assistant running in your program, but there is a huge choice of animations, including some you probably haven't seen before. There is also a good choice of option buttons, and you can even include your own buttons , which is something that you cannot do within a message box. Ultimately, you control how the Office Assistant appears and what it says!

One word of caution here: to make this example work, you must have the Office Assistant turned on in the Excel options; otherwise , this code will produce nothing.

The code is best run from a command button set into a spreadsheet. To put a command button onto a spreadsheet, select View Toolbars Control Toolbox from the spreadsheet menu. This will give you a toolbox of control icons. Find the Command Button icon and drag it onto the spreadsheet. If you use Office XP, you may need to draw it onto the spreadsheet. If you are in doubt as to which is the Command Button icon, use the tooltips to find out. Right-click the button and select Properties from the pop-up menu. Change the Caption property to read ‚“Office Assistant, ‚½ as shown in Figure 39-1.

Figure 39-1: Placing a command button on the spreadsheet to call the Office Assistant

Double-click your new button to enter the code window for the Click event. Enter the following code into the event:

 Private Sub CommandButton1_Click() 
 Dim ball As Balloon 
 Set ball = Application.Assistant.NewBalloon 
 With ball 
 .Heading = "A demonstration of Office Assistant" 
 
 .Text = "Select an option or press a button" 
 
 .Icon = msoIconTip 
 
 .Button = msoButtonSetAbortRetryIgnore 
 
 .Labels(1).Text = "Option1" 
 .Labels(2).Text = "Option2" 
 
 .Animation = msoAnimationGetArtsy 
 
 .BalloonType = msoBalloonTypeButtons 
 End With 
 result = ball.Show 
 If result = 1 Then MsgBox "You pressed Option1" 
 If result = 2 Then MsgBox "You pressed Option2" 
 If result = msoBalloonButtonAbort Then MsgBox "You pressed Abort" 
 If result = msoBalloonButtonRetry Then MsgBox "You pressed Retry" 
 If result = msoBalloonButtonIgnore Then MsgBox "You pressed Ignore" 
 
 End Sub 

A variable called ball is dimensioned as a balloon (the speech bubble that comes from the Office Assistant). The NewBalloon method then sets this to a new balloon because it has to be a new balloon over and above the existing one for the Office Assistant.

The With command allows you to customize your balloon. The Heading property is the bold text that appears at the top of the balloon. The Text property holds the general body of text for the balloon. The Icon property allows a choice of two icons or no icon, which is somewhat limited compared to the icons available on a message box. The Button property represents the buttons across the bottom of the balloon; this has a number of available constants depending on what buttons you want to show. They are all displayed in a drop-down list as you type this line in.

You can set up your own option buttons by using the Labels collection. The index refers to the label itself and the order it is displayed in, but this is also the return value if the user clicks it. In this case, Labels(1) is Option 1 and has a return value of 1. Labels(2) is Option 2 and has a return value of 2.

The Animation property dictates how the Office Assistant will look and perform ‚ in this example, the option is msoAnimationGetArtsy , which gives the Office Assistant a modern art look. Using the drop-down list when you type this in gives you a large number of options for animations, and you can experiment with these.

The BalloonType property is set to msoBalloonTypeButtons; you can also have bullet points or numbers on your labels. However, only the buttons option will accept input from the user. The balloon and Office Assistant animation is then displayed using the Show method. The variable result returns the values of the clicked buttons.

The With statement is ended, and there are then a series of If statements to respond to the value in the variable result and display a message box with an appropriate statement in it.

Once you have entered the code, return to the spreadsheet. In the Toolbox window, click the top left-hand icon, which should have a tooltip reading ‚“Exit Design Mode. ‚½ Close the toolbox window and the properties window. Click your Office Assistant button, and you should get the result shown in Figure 39-2.

Figure 39-2: A demonstration of programming the Office Assistant