Office Assistant And Msgbox Menus-2

Continued from Last Week. - Access 2003.

This article is a continuation of last week’s post, “Office Assistant and MsgBox Menus.” If you haven’t seen it, I recommend going through it first before continuing.

Last week, we learned how to use the Office Assistant for MsgBox and saw how to create a menu and present it to the User, using the Labels Property of the Balloon Object.

Labels.Text Property

We have seen that we can display the Menu from Labels.Text Properties in three different ways by setting the Values of the Balloon Type Property:

1. Balloon Type = msoBalloonTypeButtons

   The User can click on any of these buttons to select one of the options presented.

2. Balloon Type = msoBalloonTypeBullets
3. Balloon Type = msoBalloonTypeNumbers

The second and third Balloon Type values display the Labels.Text in different visual styles, but they are not interactive—users cannot select them. These Balloon types are well suited non-interactive applications where information only needs to be presented to the user, without requiring any user input.

Let’s take a closer look at the Assistant Balloon Object when displaying a Message Box to understand how it works.

If you revisit the earlier examples, you’ll notice that when a message box created with the Office Assistant’s Balloon Object is displayed, you cannot interact with any other objects in the database. You must dismiss the Balloon first by responding to one of its options—such as clicking OK or Cancel—before you can continue.

This behavior is identical to that of the standard MsgBox() Function in MS Access. However, the Office Assistant provides ways to overcome this restriction.

When a Balloon is displayed, you cannot, for instance, open a Form or Report to verify some information before clicking Yes or No, because the Mode property of the Balloon Object is set to its default value, msoModeModal.

The Mode Property of Message Balloon

So far we have avoided discussing the Message Balloon Mode Property in the earlier examples to avoid overwhelming you with too many details at once. Now that you are familiar with the basics of Message Balloons, it is easier to understand the Mode property features.

As you’ve already seen, you can create stylish Message Boxes using the Balloon Object of the Office Assistant with just a few lines of VBA code—without even using this property.

The Mode property can be assigned one of three values, each controlling how the Balloon Object behaves when displayed.

Mode = msoModeModal (default).

This setting forces the user to respond to the Balloon before taking any other action—just like a standard MsgBox in MS Access. The user must click one of the available buttons (such as OK or Cancel) to dismiss the Balloon before continuing with any other task.

Mode = msoModeModeless.

This setting allows the user to access other database objects while the Balloon remains active. However, it also requires another property—Callback—to trigger a separate subroutine that can handle user interactions, evaluate selections or choices, perform the necessary actions, and finally close the Balloon object.

Mode = msoModeAutoDown.

This setting automatically dismisses the Balloon (MsgBox) if you click anywhere else, effectively ignoring it.

The first and last Mode property values have straightforward purpose and behavior. However, the second value: msoModeModeless requires special handling. When you use this mode, set the Callback property to the name of a valid subroutine; otherwise, the Balloon will not function properly. Leaving the Callback property empty (an empty string) will also cause an error.

Let’s now modify our earlier program to demonstrate how to use the Mode property set to msoModeModeless, along with the Callback property, to handle user selections from a menu displayed in the Balloon.

The Mode and Callback Property.

The Modified Code with Mode and Callback property settings and the sample code for the required Subroutine MyProcess() is given below:

Public Sub Choices()
Dim bln As Balloon

Set bln = Assistant.NewBalloon
With bln
    .Heading = "Report Options"
    .Icon = msoIconAlertQuery
    .Button = msoButtonSetNone
    .labels(1).text = "Print Preview."
    .labels(2).text = "Print. "
    .labels(3).text = "Pivot Chart. "
    .BalloonType = msoBalloonTypeButtons
    .text = "Select one of  " & .labels.Count & " Choices? " 
    .mode = msoModeModeless
    .Callback = "myProcess"
    .Show
End With

End Sub

Sub MyProcess(bln As Balloon, lbtn As Long, lPriv As Long)
Assistant.Animation = msoAnimationPrinting
Select Case lbtn
    Case 1
        DoCmd.OpenReport "MyReport", acViewPreview
    Case 2
        DoCmd.OpenReport "MyReport", acViewNormal
    Case 3
        DoCmd.OpenReport "MyReport", acViewPivotChart
End Select
 bln.Close
End Sub

The Callback property is set to the Subroutine name myProcess. When the user clicks on one of the Options from the displayed MsgBox, the MyProcess() Subroutine is called by the Balloon and passes the required Parameter Values.

There are three parameters passed to the Subroutine when called:

1. The Balloon Object bln.
2. A Long Integer type value lbtn (BalloonTypeButton) identifying the Option clicked.
3. A Long Integer type value lPriv (Private) uniquely identifies the Balloon that is called by the Sub-Routine, if there is more than one Balloon active in memory at the same time.

NB: There is no such thing as a collection of Balloon Objects. But you can create an Array of Variables with a Balloon Object, define different Property Settings for each of them, and the Show() method is run with their respective index numbers in Programs when you need them to appear.

 lbtn  The variable will receive the user's choice value. This is tested in the Subroutine and runs the  Docmd.OpenReport  action within the Select Case...  End Select statement.

The Assistant.Animation = msoAnimationPrinting line is placed in the Subroutine, rather than in the main Program as part of the Balloon Object Property setting, to animate the printing action only after the User makes a selection from the displayed Menu; otherwise, the printing animation will run before the selection of choices.

  bln.close  The statement dismisses the MsgBox.

Next week, we will learn how to use the CheckBoxes.Text Property Values of the Balloon Object to display a Menu with Checkboxes in a MsgBox.

• Network and Report Page Setup-2
• Network and Report Page Setup
• Filter by Character and Sort
• Animating Label on Search Success
• Form Background with Gradient Color