Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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






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


No comments:

Post a Comment

Comments are subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner

Blog Directory Visit blogadda.com to discover Indian blogs


Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email


Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Class Module Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Object Reference msaccessprocess security advanced Access Security Add Custom Functions Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands function hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captur...


Blog Archive

Recent Posts