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

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.

Share:

No comments:

Post a Comment

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads

blog.feedspot.com Microsoft Access Blogs

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

User-Defined Data Type-3

Last week we have learned how to define a User -Defined Data Type and it’s usage in programs.  If you have landed on this page, you may go t...

Labels

Blog Archive

Recent Posts