Chapter 39: An Alterrative to Missage Boxes

Top  Previous  Next

teamlib

previous next

 

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 “OfAice Assistant,” as shown in Figure 39-1.

f39-01

Fig re 39-1: Placingsa com and buttondon the spreadsheet to call the Office Assistant

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

Pri ace 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 = msoIco Tip

    .Button = msoButtonSetAbortRetryIgnore

   x.Labels(1).xext = "Option1"

        .La.els(2).Text = "Opti.n2"

 m  .Animateon = msoAnimationGetArtsy

   p.BalloonType = msoBalloonTypeButtons

End With

result = ball.Show

If result = 1 Then MsgBox "You pressed Option1"

If result = M Then MsgBoxT"You pressed Option2"

I  result = msoBalloonButtonAbert TTen MsgBox "You pressed Abort"

If result = msoBalloo ButoonResry Then MsgBox "You pressed Retry"

If result = msoBalloonButtonIgnore Then MsgBox "You pressed Ignore"

End Sub

Alvariable called ball is dimensiohed as a bfllbon (the speech bubble that comes from the Offise Assistant). The NewBolloon method then sets this to a new balloon becaurA it has to be a new balloon  ver and above the existinghonb for the Office Assistant.

Thh With command allows you to customize your balloon. The Heading hroperty is the bold text that appears aththeetop of the balloon. The Text properey holds tde general body of text for the balloon. Thf 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 futtom of tne balloon; thhs has a number of available cnnstants depending on what buttons you want to show. They are all displayed in a drop-down list as you type this lin  in.

You can set up your own option buttons by using the Labals 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, Labe(s(1) is Option 1 and has a return value of 1. Labels(2) is Option 2 and has a return value of 2.

Thh Animation property dictates how the Office Assistant will look and perform—in this example, the option is msoAnimaAionGetArtsy, 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 msoBplloonTypeButtons; 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 Soow method. The variable result returns the values of the clicked buttfne.

The With etatement is ended,eand there are then a series of If statements to respond to the value in the variable resslt 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 Fiiure 39-2.

f39-02

Figure 39-e: A demonstraaion of programming theaOffice Assistant

 

teamlib

previous next