Office Assistant and MsgBox Menus

In last Week's Article: Color and Picture in Message Box we have seen a method to use Office Assistant quickly to display a Message Box with formatted text. We are not going to discuss further on 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 needs 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


2. Message Box with Options Menu


3. Office Assistant with CheckBox Menu

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.


Following are some of these properties:

• Animation


• Icon


• Heading


• Text


• BalloonType


• Button

NB: If you have not already attached the Microsoft Office Object Library to your Database, to 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 check mark to select it.


• Click OK to close the Dialog Box.

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 BalloonType 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 with one of the above values. The BalloonType is used with Labels Property only. We will look into the Labels property at the later 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, 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

Press Alt+F11 to display the VBA Editing Window. Select Module 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 in 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.


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 ReportProcess 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 User’s response. See the example code given below:

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 appropriate changes before attempting to Run it.




The dimension of the Labels() Property Value can be up to a maximum of 5 only. The BalloonType Value msoBalloonTypeButtons allows 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.


There are two more BalloonType Property values available: msoBalloonTypeBullets and msoBalloonTypeNumbers. These are not selectable, like msoBalloonTypeButtons, and 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 BalloonType 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