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:
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:
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 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.
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.