Office Assistant and MsgBox Menus

Introduction - Access 2003.

In last Week's Article: Color and Picture in Message Box we have seen some methods to use the Office Assistant quickly, to display a Message Box with formatted text. We are not going to discuss further the formatting part, but I have a general feeling that Readers would like to know how this simple method can be used to obtain responses from Users, out of several options presented to them, and do different things based on their selection of choices.

I have already covered this topic by creating Functions like MsgOK(), MsgYN(), MsgOKCL(), etc., with the use of Office Assistant. These can be called with only the Message Text Value alone or Message Text and Title Values as Parameters, from anywhere within the Application like the built-in Function MsgBox(). I made an attempt to simplify the usage of Office Assistant through the above-mentioned Functions and others, which otherwise need several property values to be passed to the Office Assistant's Balloon Object for displaying.

But, in the simplification process, the method used in those Functions is not fully understood by several readers.

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

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

In the example code presented in last week's Post: Color and Picture in Message Box, we came across several Properties of the Balloon Object of Office Assistant that can be set with values before the Message Box is displayed.

We are going to work with these properties directly so that it is easy to understand their usage, rather than passing values for them through the Parameter List in the function definition.

Message Balloon Properties

Following are some of these properties:

• Animation
• Icon
• Heading
• Text
• Balloon Type
• Button

Microsoft Office Object Library

NB: If you have not already attached the Microsoft Office Object Library to your Database, try out the examples given here, then do that by following the procedure given below.

• Press Alt+F11 to display the VBA Code Window (or Tools - - >Macro- ->Visual Basic Editor).
• Select References from Tools Menu.
• Find Microsoft Office Object Library in the Available List and put a checkmark to select it.
• Click OK to close the Dialog Box.

Message Balloon Animation Constants

The value for Animation alone has about thirty-five different choices which are defined as Constants in the Microsoft Office Object Library. The constant values for Balloon Properties Animation, Button, Icon, and Balloon Type are given below for reference:

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

Animation and Icon properties are always set with one of the above values based on what we are trying to convey to the User. By default, the OK button will appear. If any other Button or Buttons Group is required, then the Button Property must be set to one of the above values. The Balloon Type is used with Labels Property only. We will look into the Labels property in the latter part of this Article.

I am sure when you go through these simple, straightforward examples you will be better informed about the usage of Office Assistant, and its methods and will start using them in your Applications.

I will reproduce the Code here with simple changes, which we have seen in last week's article, and go through it before we make changes in them 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 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 setting the other Property Values. The Text property value shows the body text of the Message Box and Heading is the Title Text in Bold letters. The Animation property can be set with one of the 35 different options given above. The Icon Property value we have changed to Information type here.

If we want to obtain the responses of the User to do different things then we need to bring the Button Property into the Code. Let us say if the User needs to proceed with the report preparation process then she must click the OK Button otherwise Cancel Button. To evaluate the response received from the User and to take action accordingly we must write code for that further down in the Routine.

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 one to find the difference.

If the user clicked the OK Button (-1 is returned in Variable R, Cancel=-2) then the Report Process Macro is run otherwise the Program ends doing nothing.

We can give different choices to the User in the form of a Menu with the use of Labels Property and perform the actions based on the User's responsibility. See the example code given 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 dimension of the Labels() Property Value can be up to a maximum of 5 only. The Balloon Type Value msoBalloon Type Buttons allow the User to click on one of the Options to select it and the Index value of the item clicked is returned in Variable R. We have set the Value Button = msoButtonSetNone to remove the OK Button from appearing so that the User will click only on one of the Options displayed.

Balloon Type Bullets and Numbers.

There are two more Balloon Type Property values available: msoBalloonTypeBullets and msoBalloonTypeNumbers. These are not selectable, like msoBalloon Type Buttons, and are used 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:

We will continue this discussion next week to see more ways to use the Office Assistant with Labels and CheckBoxes.

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