XL2000: How to Display a Custom Office Assistant Balloon (213731)

MORE INFORMATION

Microsoft provides programming examples for illustration only, without warranty either expressed or implied, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Partner or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Partners, please visit the following Microsoft Web site: For more information about the support options that are available and about how to contact Microsoft, visit the following Microsoft Web site: To create a custom message balloon for the Office Assistant, open a Visual Basic module and then create a macro for the type of balloon you want. The following examples are of the most common custom balloon types:

Step 1: Creating the Visual Basic Module

1. Open a new workbook.
2. Press ALT+F11 to start the Visual Basic Editor.
3. On the Insert menu, click Module.

Step 2: Creating a Balloon

Example 1: Creating a Simple Balloon

1. Type the following code in the module:

   Sub Simple_Balloon()
   
       ' Create the balloon object.
       Set myballoon= Assistant.NewBalloon
   
       With myballoon
   
           .Heading = "My Balloon"
           .Text = "Click OK to close the balloon"
           .Show
       End With
   
   End Sub
   					

2. Run the Simple_Balloon macro. The Office Assistant displays a balloon with the heading "My Balloon" in bold letters and a message that prompts you to click OK.
   
   
3. Click OK. The balloon disappears.

Example 2: Creating a Balloon with a Numbered List

1. Type the following code in the module:

   Sub List_Balloon()
   
       ' Create the balloon object.
       Set myballoon= Assistant.NewBalloon
   
       With myballoon
   
           .Heading = "My Balloon"
           .Text = "Click OK to close the balloon."
   
           ' Text for the list items:
           .Labels(1).Text = "Click Save on the File menu to save " _
               & "the file."
           .Labels(2).Text = "Click Print on the File menu to " _
               & "print the file."
   
           ' Create a numbered list.
           .BalloonType = msoBalloonTypeNumbers
   
           ' Display the balloon.
           .Show
       End With
   
   End Sub
   					

2. Run the List_Balloon macro. The Office Assistant displays a balloon with a number list.
   
   
3. Click OK to close the balloon.

Example 3: Creating a Balloon with Check Boxes

1. Type the following code in the module:

   Sub Balloon_Checkboxes()
   
       ' Create balloon object.
       Set myballoon = Assistant.NewBalloon
   
       With myballoon
           .Heading = "Regional Sales Data"
           .Text = "Select your region"
   
           ' Place OK and Cancel buttons at bottom of the balloon.
           .Button = msoButtonSetOkCancel
   
           For i = 1 To 3
               .CheckBoxes(i).Text = "Region " & i
           Next
   
           ' Display the balloon and assign it to x.
           x = .Show
   
           i = 0
           Select Case x
   
               Case -1 'You clicked OK.
   
                   ' Loop through check boxes on the balloon.
                   For Each y In .CheckBoxes
                       i = i + 1
   
                       If y.Checked Then
                           MsgBox "region " & i
                       End If
                   Next
   
               Case -2 ' You clicked Cancel.
   
                   MsgBox "You clicked Cancel."
           End Select
      End With
   End Sub
   					

2. Run the Balloon_Checkboxes macro.
   
   A balloon with three check boxes appears.
3. Click one or more check boxes, and then click OK.
   
   Messages boxes that display your choices appear. If you click Cancel, a message box with a message that indicates you clicked Cancel appears.

Example 4: Creating Multiple Balloons

1. Type the following code in the module:

   Sub Balloon_Array()
   
       Dim myBalloonArray(2) As Balloon
   
       With Assistant
           For i = 1 To 2
               ' Create a new balloon object.
               Set myBalloonArray(i) = .NewBalloon
           Next
       End With
   
       With myBalloonArray(1)
           .Heading = "This is balloon #1."
           .Text = "Click OK to close the balloon."
   
           ' Display the first balloon.
           .Show
       End With
   
       With myBalloonArray(2)
           .Heading = "This is balloon #2."
           .Text = "Click OK to close the balloon."
   
           ' Display the second balloon.
           .Show
       End With
   
   End Sub
   					

2. Run the Balloon_Array macro.
   
   The first balloon appears.
3. Click OK in the balloon.
   
   The second balloon appears.
4. Click OK in the balloon.
   
   The second balloon is dismissed.

Step 3: Selecting and Using a Balloon

1. Type the following code in the module:

   Sub Balloon_Options()
   
       ' Create a new balloon object.
       Set balloon1 = Assistant.NewBalloon
   
       With balloon1
           .Heading = "First Balloon"
   
           ' Create buttons for the list items.
           .BalloonType = msoBalloonTypeButtons
   
           .Text = "Click an option or click OK to close the balloon."
   
           ' Set the text for list items.
           .Labels(1).Text = "Save your file."
           .Labels(2).Text = "Print your file."
   
           ' Mode must be msoModeModeless in order to use Callback
           ' property.
           .Mode = msoModeModeless
   
           ' Call the "ProcessOption" macro when a balloon list item is
           ' clicked.
           .Callback = "ProcessOption"
   
           .Show
       End With
   
   End Sub
   
   ' Every procedure specified in the Callback property is
   ' passed three arguments: the balloon that activated the
   ' procedure, the return value of the button the user pressed,
   ' and an integer that uniquely identifies the balloon that
   ' called the procedure.
   
   Sub ProcessOption(bln As Balloon, ibtn As Long, iPriv As Long)
   
       ' bln: the name of the balloon that activated the procedure
       ' ibtn: the number of the button clicked in the balloon
       ' iPriv: an integer that uniquely identifies the balloon
   
       Assistant.Animation = msoAnimationGoodbye
   
       Select Case ibtn
           Case 1
               ' Insert your routine here.
               bln.Close
               bln.Text = "You chose the Save option."
               bln.Show
           Case 2
               ' Insert your routine here.
               bln.Close
               bln.Text = "You chose the Print option."
               bln.Show
   
           Case Else ' You clicked something other than a list item.
               bln.Close
   
       End Select
   
   End Sub
   					

2. Run the Balloon_Options macro.
   
   A balloon with two options appears.
3. Click Save your file.
   
   The balloon reappears, and the text in the balloon is "You chose the Save option."
4. Click Print your file.
   
   The balloon reappears, and the text in the balloon is "You chose the Print option."
5. Click OK to dismiss the balloon.