Office Space: Tips and Tricks for Scripting Microsoft Office Applications

Welcome to Office Space, the column that offers tips and tricks for scripting Microsoft® Office applications. We’ll post new tips every Tuesday and Thursday; to see an archive of previous tips, visit the Office Space Archive. And if you have particular questions about Microsoft Office scripting, feel free to send them to scripter@microsoft.com (in English, if possible). We can’t promise to answer all the questions we receive, but we’ll do our best.

Animating the Microsoft Office Assistant (Part 2)

Note: The Office Assistant has been removed from Office 2007, and therefore this script will not work on that version of Microsoft Office.

A couple weeks ago we wrote an Office Space column that introduced you to the Microsoft Assistant and showed you ways to incorporate that assistant into your scripts. At the same time we noted that it was possible to add check boxes to the Assistant’s word balloon, and we promised to come back at some point and show you how to do that.

As you might expect, ever since we made that announcement there have been hundreds and hundreds of people camped out on the lawn outside the Scripting Guys offices, each of these people hoping to be the first to find out how to add check boxes to the Assistant’s word balloons. (Well, at least we assume there are hundreds of people camped outside our offices. We always use the side door, so we don’t really know for sure.)

If you happen to be one of those people camped outside, then it’s time to pull up the tent stakes, put out the campfire, and head for home. (Incidentally, is anyone going to eat that last hamburger?) Today – at long last – we’ll show you how to add check boxes to the Assistant’s word balloons.

We appreciate that. But we don’t really do groups hugs ….

If you didn’t read the first part of this series (the one introducing the Microsoft Assistant), you should probably do that right now; we’re not going to revisit the basic code for displaying and interacting with the Assistant. (You should probably also ask yourself why the heck you’ve been camping out on our lawn the past couple weeks.) For those of you who did read the first article, here’s a simple script that displays three check boxes in the Assistant’s word balloon:

Const msoButtonSetOkCancel = 3
Const msoBalloonButtonOK = -1 

Set objExcel = CreateObject("Excel.Application") 
objExcel.Visible = True 

objExcel.Assistant.On = True
objExcel.Assistant.Visible = True 
objExcel.Assistant.Sounds = True

Wscript.Sleep 2000

Set objBalloon = objExcel.Assistant.NewBalloon 
objBalloon.Heading = "Welcome to Excel"
objBalloon.Button = msoButtonSetOkCancel 
objBalloon.Text = "Please select one or more of the following options:" 
objBalloon.CheckBoxes(1).Text = "Option A"
objBalloon.CheckBoxes(2).Text = "Option B"
objBalloon.CheckBoxes(3).Text = "Option C"

objBalloon.Show 

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
  
objExcel.Assistant.Animation = 3
objExcel.Assistant.Visible = False
objExcel.Assistant.On = False

If you’re looking for rudimentary, you came to the right place: although the script displays three check boxes in the word balloon, it takes the same action (creating a new worksheet in Excel) regardless of which check boxes you select or don’t select. But don’t worry; we’ll get to that in a minute.

For now, let’s focus on displaying the check boxes. The script starts out by defining a pair of constants. The first – msoButtonSetOkCancel – will be used to display OK and Cancel buttons in the word balloon; the second – msoBalloonButtonOK – will be used to determine whether or not the user clicked the OK button. Like we said, in this particular script it doesn’t make any difference which button the user clicked; later on, it will.

The remainder of the script is the same code we used last time around, with the exception of these three lines:

objBalloon.CheckBoxes(1).Text = "Option A"
objBalloon.CheckBoxes(2).Text = "Option B"
objBalloon.CheckBoxes(3).Text = "Option C"

As you probably figured out, this is the code that actually adds the three check boxes to the word balloon. Adding a check box is remarkably easy: you simply specify the index number of the check box along with the accompanying label for that check box. Because the first check box in the CheckBoxes collection is given the index number 1, this code adds the first check box to the collection and assigns it the label Option A:

objBalloon.CheckBoxes(1).Text = "Option A"

Suppose you already have 9 check boxes in the collection and now you want to add a 10th, this one with the label Option Number 10? Why not:

objBalloon.CheckBoxes(10).Text = "Option Number 10"

After adding the check boxes we call the Show method; this displays the Assistant and its word balloon. We then wait for the user to click either the OK or Cancel button, a process that looks remarkably like this:

As soon as a button is clicked the script adds a new worksheet to our instance of Excel and then dismisses the Assistant. If that wasn’t worth camping outside on the lawn for the past two week we’d like to know what is.

By the way, is that leftover hot dog being saved for someone or …?

Well, yes, we guess you do have a point: the script might be a bit more compelling if it could actually determine which check boxes (if any) were selected, and then take action based on those selections. Is that even possible? Of course it is:

Const msoButtonSetOkCancel = 3
Const msoBalloonButtonOK = -1 

Set objExcel = CreateObject("Excel.Application") 
objExcel.Visible = True 

objExcel.Assistant.On = True
objExcel.Assistant.Visible = True 
objExcel.Assistant.Sounds = True

Wscript.Sleep 2000

Set objBalloon = objExcel.Assistant.NewBalloon 
objBalloon.Heading = "Welcome to Excel"
objBalloon.Button = msoButtonSetOkCancel 
objBalloon.Text = "Please select one or more of the following options:" 
objBalloon.CheckBoxes(1).Text = "Option A"
objBalloon.CheckBoxes(2).Text = "Option B"
objBalloon.CheckBoxes(3).Text = "Option C"

If objBalloon.Show = msoBalloonButtonOK Then 
    If objBalloon.Checkboxes(1).Checked Then
       strMessage = "You selected Option A." & vbCrLf
    End If
    If objBalloon.Checkboxes(2).Checked Then
       strMessage = strMessage & "You selected Option B." & vbCrLf
    End If
    If objBalloon.Checkboxes(3).Checked Then
       strMessage = strMessage & "You selected Option C."
    End If   
    Wscript.Echo strMessage
End If     

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
  
objExcel.Assistant.Animation = 3
objExcel.Assistant.Visible = False
objExcel.Assistant.On = False

All we’ve done here is modify the part of the script where the Assistant actually “speaks” (that is, the part where the word balloon is displayed). In our first script, all we did was call the Show method and wait for a button to be clicked. This time around, however, we call the Show method and check to see if the OK button was clicked. We do that by determining whether or not the return value for the word balloon button is equal to msBalloonButtonOK, the constant we set way back at the beginning of the script:

If objBalloon.Show = msoBalloonButtonOK Then

If the OK button was not clicked then we simply skip the entire If-Then block, add a new worksheet, and then get rid of the Office Assistant.

But what if the user did click the OK button? In that case, we have three If-Then statements that check to see if any (or all) of the check boxes were selected. We do that simply by seeing whether or not the Checked property of each individual check box (referenced by index number) is True:

If objBalloon.Checkboxes(1).Checked Then
   strMessage = "You selected Option A." & vbCrLf
End If

For each check box that was checked we add a note to a message stored in a variable named strMessage. After checking the status of each check box we then echo back a message box that indicates the check boxes that were selected:

That’s really all there is to it. (Note that, because we use Wscript.Echo, if you run this script from a command window using Cscript the output will display in the command window, not in a message box.)

And, yes, had we wanted to we could have had the Assistant report back which options were selected. Here’s a revised script that does that very thing. After constructing the message the script uses the Close method to dismiss our original word balloon (the one with the three check boxes), then creates and displays a new word balloon:

Const msoButtonSetOkCancel = 3
Const msoBalloonButtonOK = -1 

Set objExcel = CreateObject("Excel.Application") 
objExcel.Visible = True 

objExcel.Assistant.On = True
objExcel.Assistant.Visible = True 
objExcel.Assistant.Sounds = True

Wscript.Sleep 2000

Set objBalloon = objExcel.Assistant.NewBalloon 
objBalloon.Heading = "Welcome to Excel"
objBalloon.Button = msoButtonSetOkCancel 
objBalloon.Text = "Please select one or more of the following options:" 
objBalloon.CheckBoxes(1).Text = "Option A"
objBalloon.CheckBoxes(2).Text = "Option B"
objBalloon.CheckBoxes(3).Text = "Option C"

If objBalloon.Show = msoBalloonButtonOK Then 
    If objBalloon.Checkboxes(1).Checked Then
       strMessage = "You selected Option A." & vbCrLf
    End If
    If objBalloon.Checkboxes(2).Checked Then
       strMessage = strMessage & "You selected Option B." & vbCrLf
    End If
    If objBalloon.Checkboxes(3).Checked Then
       strMessage = strMessage & "You selected Option C."
    End If   
    objBalloon.Close
    Set objBalloon2 = objExcel.Assistant.NewBalloon 
    objBalloon2.Text = strMessage
    objBalloon2.Show
End If     

Set objWorkbook = objExcel.Workbooks.Add()
Set objWorksheet = objWorkbook.Worksheets(1)
  
objExcel.Assistant.Animation = 3
objExcel.Assistant.Visible = False
objExcel.Assistant.On = False

And here’s what that second balloon looks like onscreen:

Very cool, huh?

There are actually a few other little tricks we can do with the Office Assistant, but we won’t make any promises about those; Microsoft security didn’t seem too happy about all those people camping out on the lawn. But we’ll see what we can do.

In the meantime, if you’re just going to throw that potato salad away, well ….