Office Assistant and MsgBox Menus ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction - Access 2003.

In last week’s article, Color and Picture in Message Box, we explored several techniques to use the Office Assistant for displaying message boxes with formatted text. In this article, we will move beyond formatting. I believe many readers would now like to learn how this simple feature can be used to gather responses from users — allowing them to choose from multiple options and trigger different actions based on their selections.

I have already discussed this concept earlier by creating MsgOK(), MsgYN(), and MsgOKCL() Functions, and others were created using the Office Assistant. These functions can be called from anywhere within your application — just like the built-in MsgBox() function — using only the message text, or both the message text and title as parameters.

My intention in designing these functions was to simplify the use of the Office Assistant’s Balloon object, which otherwise requires several property values to be manually set for each display.

However, during this simplification process, I’ve found that many readers may not fully understand the underlying method used within these functions.

The Links to those earlier posts are given below for reference:

Message Box with Office Assistant -Access 2003
Message Box with Options Menu - Access 2003
Office Assistant with CheckBox Menu - Access 2003.

In the example code presented in last week’s article, Color and Picture in Message Box, we explored several property values of the Balloon object of the Office Assistant that can be assigned before displaying a message box.

In this session, we will work directly with these properties so that you can clearly understand how each one functions. This approach will make it easier to grasp their behavior and usage, rather than passing values for them indirectly through the parameter list of a function definition.

Message Balloon Properties

The following are some of these properties:

Animation
Icon
Heading
Text
Balloon Type
Button

Microsoft Office Object Library

Note:
If you have not yet attached the Microsoft Office Object Library to your database, please do so before trying out the examples given here. Follow the steps below:

Press Alt + F11 to open the VBA Code Window (or go to Tools → Macro → Visual Basic Editor).
From the Tools menu, select References.
In the Available References list, locate Microsoft Office Object Library and place a check mark beside it.
Click OK to close the dialog box.

Message Balloon Animation Constants

The Animation property alone offers about thirty-five different choices, all defined as constants in the Microsoft Office Object Library. For reference, the constant values for the Balloon properties — Animation, Button, Icon, and Balloon Type — are listed below.

Animation	Icon
msoAnimationAppear msoAnimationBeginSpeaking msoAnimationCharacterSuccessMajor msoAnimationCheckingSomething msoAnimationDisappear msoAnimationEmptyTrash msoAnimationGestureDown msoAnimationGestureLeft msoAnimationGestureRight msoAnimationGestureUp msoAnimationGetArtsy msoAnimationGetAttentionMajor msoAnimationGetAttentionMinor msoAnimationGetTechy msoAnimationGetWizardy msoAnimationGoodbye msoAnimationGreeting msoAnimationIdle msoAnimationListensToComputer msoAnimationLookDown msoAnimationLookDownLeft msoAnimationLookDownRight msoAnimationLookLeft msoAnimationLookRight msoAnimationLookUp msoAnimationLookUpLeft msoAnimationLookUpRight msoAnimationPrinting msoAnimationRestPose msoAnimationSaving msoAnimationSearching msoAnimationSendingMail msoAnimationThinking msoAnimationWorkingAtSomething msoAnimationWritingNotingSomething	msoIconAlert msoIconAlertCritical msoIconAlertInfo msoIconAlertQuery msoIconAlertWarning msoIconNone msoIconTip
Button	BalloonType
msoButtonSetAbortRetryIgnore msoButtonSetBackClose msoButtonSetBackNextClose msoButtonSetBackNextSnooze msoButtonSetCancel msoButtonSetNextClose msoButtonSetNone msoButtonSetOK msoButtonSetOkCancel msoButtonSetRetryCancel msoButtonSetSearchClose msoButtonSetTipsOptionsClose msoButtonSetYesAllNoCancel msoButtonSetYesNo msoButtonSetYesNoCancel	msoBalloonTypeButtons msoBalloonTypeBullets msoBalloonTypeNumbers

The Animation and Icon properties are always set using one of the values listed above, depending on the message you want to convey to the User. By default, the OK button appears. If you need a different button or a group of buttons, the Button property must be set to one of the available values. The Balloon Type property is used in conjunction with the Labels property, which will be covered later in this article.

By going through these simple and straightforward examples, you will gain a better understanding of the Office Assistant, its methods, and how to incorporate them into your Applications.

We will now reproduce the code from last week’s article with minor adjustments and review it before applying changes for our new examples.

Public Sub MyMsgBox()
Dim strMsg As String
Dim strTitle As String

strTitle = "Assistant Test"
strMsg = "Wecome to MS-Access Tips and Tricks"

With Assistant.NewBalloon
    .Icon = msoIconAlertInfo
    .Animation = msoAnimationGetAttentionMajor
    .Heading = strTitle
    .text = strMsg
    .Show
End With

End Sub

Use the Above Code for a Demo Run

Press Alt+F11 to display the VBA Editing Window. Select the Module option from the Insert Menu to create a new Standard VBA Module. Copy and Paste the above Code into the Module. Click somewhere in the middle of the Code and press F5 to run it.

In the above example, the Show() method displays the Message Box after all the other property values have been set. The Text property defines the main body text of the Message Box, while the Heading property sets the title text in bold. The Animation property can be assigned one of the 35 options listed above, and we have set the Icon property to the Information type.

To obtain the User’s response and take different actions accordingly, we need to use the Button property in the code. For example, if the User must proceed with the report preparation process, they would click the OK button; otherwise, they might click Cancel. We can then evaluate the response returned by the Balloon Object and write further code in the routine to handle each scenario appropriately.

Modified VBA Code

Let us see how we can do this with changes to the above Code. The modified program is given below:

Public Sub MyMsgBox ()
Dim strMsg As String
Dim strTitle As String
Dim R As Long

Title = "Assistant Test"
msgTxt = "Proceess Weekly Reports...?"

With Assistant.NewBalloon
.Icon = msoIconAlertQuery
    .Animation = msoAnimationGetAttentionMajor 
 .Button = msoButtonSetOkCancel
    .Heading = strTitle
    .text = strMsg 
 R = .Show
End With

If R = -1 Then
'User Clicked OK Button
    DoCmd.RunMacro "ReportProcess"
End If
End Sub

Compare the changes made in the new code with the earlier version to identify the differences.

If the user clicks the OK button (which returns -1 in the variable R; Cancel returns -2, when the Report Process Macro is executed. Otherwise, the program ends without performing any action.

We can also present different choices to the user as a menu using the Labels property and execute actions based on the user’s selection. An example of such code is provided below:

MsgBox-Based Menu Choices.

Public Sub Choices()
Dim R As Long
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?"
    R = .Show
End With

Select Case R
    Case 1
        DoCmd.OpenReport "MyReport", acViewPreview
    Case 2
        DoCmd.OpenReport "MyReport", acViewNormal
    Case 3
        DoCmd.OpenReport "MyReport", acViewPivotChart
End Select

End Sub

Note: You may copy and paste the code into the VBA Module and modify it with the appropriate changes before attempting to run it.

The Labels() property can hold a maximum of 5 items. Using the Balloon Type value: msoBalloonTypeButtons The user can click on one of the options to make a selection. The index of the clicked item is returned in the variable R.

In this example, we set Button = msoButtonSetNone to remove the default OK button, ensuring that the user must select one of the options displayed.

Balloon Type Bullets and Numbers.

Two additional Balloon Type property values are available: msoBalloonTypeBullets and msoBalloonTypeNumbers. Unlike msoBalloonTypeButtons These are not selectable by the user and are intended only for displaying information.

Compare the following Code with the earlier one to see the difference in changed Property Values.

Public Sub InfoDisplay()
Dim bln As Balloon

Set bln = Assistant.NewBalloon
With bln
    .Heading = "Reminder"
    .Icon = msoIconAlertInfo
    .labels(1).text = "MIS Reports."
    .labels(2).text = "Trial Balance."
    .labels(3).text = "Balance Sheet."
    .BalloonType = msoBalloonTypeBullets
    .text = "Monthly Reports for User Departments"
    .Button = msoButtonSetOK
    .Show
End With

Sample Images with Balloon Type Property Changes are given below: