Office Assistant And Msgbox Menus-2

Continued from Last Week. - Access 2003.

This article is a continuation of last week’s post, “Office Assistant and MsgBox Menus.” If you haven’t seen it, I recommend going through it first before continuing.

Last week, we learned how to use the Office Assistant for MsgBox and saw how to create a menu and present it to the User, using the Labels Property of the Balloon Object.

Labels.Text Property

We have seen that we can display the Menu from Labels.Text Properties in three different ways by setting the Values of the Balloon Type Property:

1. Balloon Type = msoBalloonTypeButtons

   The User can click on any of these buttons to select one of the options presented.

2. Balloon Type = msoBalloonTypeBullets
3. Balloon Type = msoBalloonTypeNumbers

The second and third Balloon Type values display the Labels.Text in different visual styles, but they are not interactive—users cannot select them. These Balloon types are well suited non-interactive applications where information only needs to be presented to the user, without requiring any user input.

Let’s take a closer look at the Assistant Balloon Object when displaying a Message Box to understand how it works.

If you revisit the earlier examples, you’ll notice that when a message box created with the Office Assistant’s Balloon Object is displayed, you cannot interact with any other objects in the database. You must dismiss the Balloon first by responding to one of its options—such as clicking OK or Cancel—before you can continue.

This behavior is identical to that of the standard MsgBox() Function in MS Access. However, the Office Assistant provides ways to overcome this restriction.

When a Balloon is displayed, you cannot, for instance, open a Form or Report to verify some information before clicking Yes or No, because the Mode property of the Balloon Object is set to its default value, msoModeModal.

The Mode Property of Message Balloon

So far we have avoided discussing the Message Balloon Mode Property in the earlier examples to avoid overwhelming you with too many details at once. Now that you are familiar with the basics of Message Balloons, it is easier to understand the Mode property features.

As you’ve already seen, you can create stylish Message Boxes using the Balloon Object of the Office Assistant with just a few lines of VBA code—without even using this property.

The Mode property can be assigned one of three values, each controlling how the Balloon Object behaves when displayed.

Mode = msoModeModal (default).

This setting forces the user to respond to the Balloon before taking any other action—just like a standard MsgBox in MS Access. The user must click one of the available buttons (such as OK or Cancel) to dismiss the Balloon before continuing with any other task.

Mode = msoModeModeless.

This setting allows the user to access other database objects while the Balloon remains active. However, it also requires another property—Callback—to trigger a separate subroutine that can handle user interactions, evaluate selections or choices, perform the necessary actions, and finally close the Balloon object.

Mode = msoModeAutoDown.

This setting automatically dismisses the Balloon (MsgBox) if you click anywhere else, effectively ignoring it.

The first and last Mode property values have straightforward purpose and behavior. However, the second value: msoModeModeless requires special handling. When you use this mode, set the Callback property to the name of a valid subroutine; otherwise, the Balloon will not function properly. Leaving the Callback property empty (an empty string) will also cause an error.

Let’s now modify our earlier program to demonstrate how to use the Mode property set to msoModeModeless, along with the Callback property, to handle user selections from a menu displayed in the Balloon.

The Mode and Callback Property.

The Modified Code with Mode and Callback property settings and the sample code for the required Subroutine MyProcess() is given below:

Public Sub Choices()
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? " 
    .mode = msoModeModeless
    .Callback = "myProcess"
    .Show
End With

End Sub

Sub MyProcess(bln As Balloon, lbtn As Long, lPriv As Long)
Assistant.Animation = msoAnimationPrinting
Select Case lbtn
    Case 1
        DoCmd.OpenReport "MyReport", acViewPreview
    Case 2
        DoCmd.OpenReport "MyReport", acViewNormal
    Case 3
        DoCmd.OpenReport "MyReport", acViewPivotChart
End Select
 bln.Close
End Sub

The Callback property is set to the Subroutine name myProcess. When the user clicks on one of the Options from the displayed MsgBox, the MyProcess() Subroutine is called by the Balloon and passes the required Parameter Values.

There are three parameters passed to the Subroutine when called:

1. The Balloon Object bln.
2. A Long Integer type value lbtn (BalloonTypeButton) identifying the Option clicked.
3. A Long Integer type value lPriv (Private) uniquely identifies the Balloon that is called by the Sub-Routine, if there is more than one Balloon active in memory at the same time.

NB: There is no such thing as a collection of Balloon Objects. But you can create an Array of Variables with a Balloon Object, define different Property Settings for each of them, and the Show() method is run with their respective index numbers in Programs when you need them to appear.

 lbtn  The variable will receive the user's choice value. This is tested in the Subroutine and runs the  Docmd.OpenReport  action within the Select Case...  End Select statement.

The Assistant.Animation = msoAnimationPrinting line is placed in the Subroutine, rather than in the main Program as part of the Balloon Object Property setting, to animate the printing action only after the User makes a selection from the displayed Menu; otherwise, the printing animation will run before the selection of choices.

  bln.close  The statement dismisses the MsgBox.

Next week, we will learn how to use the CheckBoxes.Text Property Values of the Balloon Object to display a Menu with Checkboxes in a MsgBox.

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

Read More

Office Assistant and MsgBox Menus

Office Assistant and MsgBox Menus - Access 2003.

In last week’s article, Color and Picture in Message Box, we explored several techniques to use the Office Assistant for displaying message boxes with formatted text. In this article, we will move beyond formatting. I believe many readers would like to learn how this simple feature can gather User responses—allowing them to choose from multiple options and trigger different actions based on their selections.

I have already discussed this concept before by creating MsgOK(), MsgYN(), and MsgOKCL() Functions, and others were created using the Office Assistant. These functions can be called from anywhere within your application — just like the built-in MsgBox() function — using only the message text, or both the message text and title as parameters.

I designed these functions to simplify the Office Assistant’s Balloon object use, which otherwise requires manually setting several property values for each display.

However, during this simplification process, I’ve found that many readers may not fully understand the underlying method in these functions.

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 article, Color and Picture in Message Box, we explored several property values of the Balloon object of the Office Assistant that can be assigned before displaying a message box.

In this session, we will work directly with these properties so that you can clearly understand how each one works. This approach is easier to understand and use than passing the values indirectly through the Function parameter list.

Message Balloon Properties.

The following are some of these properties:

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

Microsoft Office Object Library

Note:
If you have not yet attached the Microsoft Office Object Library to your database, please do so before trying out the examples given here. Follow the steps below:

1. Press Alt + F11 to open the VBA Code Window (or go to Tools → Macro → Visual Basic Editor).

2. From the Tools menu, select References.

3. In the Available References list, locate the Microsoft Office Object Library file and select it.

4. Click OK to close the dialog box.

Message Balloon Animation Constants.

The Animation property alone offers about thirty-five different choices, all defined as constants in the Microsoft Office Object Library. For reference, the constant values for the Balloon properties — Animation, Button, Icon, and Balloon Type — are listed below.

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

The Animation and Icon properties are always set using one of the values listed above, depending on the message you want to convey to the User. By default, the OK button appears. If you need a different button or a group of buttons, then set the Button property values. The Balloon Type property is used in conjunction with the Labels property, which will be covered later in this article.

By going through these simple and straightforward examples, you will gain a better understanding of the Office Assistant, its methods, and how to incorporate them into your Applications.

I will now reproduce the vba code from last week’s Article with minor adjustments and review it before applying changes 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 the Insert Menu to create a new Standard VBA Module. Copy and Paste the above Code into the Module. Click anywhere in the Code and press F5 to run.

Right-click to open the Large Image in a New Window.

The Show() method of the Assistant.NewBalloon, in this sample function, displays the Message Box after the relevant Property values have been assigned. The Text property defines the Message Box Main Body Text, while the Heading property specifies the Title text, which is displayed in Bold. One option out of 35 options is assigned to the Animation Property, and the Icon property is set to the Information type.

To obtain the User’s response and take different actions accordingly, we need to use the Button property in the code. For example, if the User must proceed to the report preparation process, they would click the OK Button; otherwise, they might click Cancel. We can then evaluate the response from the Balloon Object and write further Code in the routine to handle each scenario appropriately.

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

Right-click to open the Large Image in a New Window.

Compare the changes made in the new code with the earlier version to identify the differences.

If the user clicks the OK button (which returns -1 in the variable R; Cancel returns -2 when the Report Process Macro is executed. Otherwise, the program ends without performing any action.

We can also present different choices to the user as a menu using the Labels property and execute actions based on the user’s selection. An example of such code is provided 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: Copy and paste the code into the Module, and modify it before running.

Right-click to open the Large Image in a New Window.

The Labels() property can hold a maximum of 5 items using the Balloon Type value: msoBalloonTypeButtons. The user can click on one of the given options to make a selection. The index number of the clicked item is returned in the variable R.

In this example, we set Button = msoButtonSetNone to remove the default OK button, ensuring that the user must select one of the options displayed.

Balloon Type Bullets and Numbers.

Two additional Balloon Type property values are available: msoBalloonTypeBullets and msoBalloonTypeNumbers. Unlike msoBalloonTypeButtons These are not selectable by the user and are intended 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:

Right-click to open the Large Image in a New Window.

We will continue this discussion next week to explore 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

Read More

Color and Picture in Message Box

Color and Picture in Message Box - Access 2003

The image below shows a Microsoft Access Message Box displayed using the Office Assistant. This Message Box appears after the user changes the Appointment Date field to a new value. The alert prompts the user to reconfirm the change to take effect.

Right-click to open the Large Image in a New Window.

The field name is underlined, and the old and new values are displayed in different colors. The company logo appears above the message text.

A Quick Demo

Want to find out how this trick works?

1. Copy the following VBA Code into a Standard Module in your Database:
   

   Public Function MyAssistant()
   Dim msg As String
   Dim AppointmentDt As Date
   Dim AppointmentDt2 As Date
   Dim logo As String
   
   AppointmentDt = #7/20/2009#
   AppointmentDt2 = #7/25/2009#
   logo = "{bmp D:\Images\CoLogo.bmp}"
   
   msg = logo & vbCr & "Existing {ul 1}Appointment Date:{ul 0}{cf 252} " & AppointmentDt & "{cf 0}" & vbCr & vbCr
   msg = msg & "Replace with {ul 1}New Date:{ul 0}{cf 249} " & AppointmentDt2 & "{cf 0}...?"
   
   With Assistant.NewBalloon
       .Icon = msoIconAlertQuery
       .Animation = msoAnimationGetAttentionMinor
       .Heading = "Appointment Date"
       .text = msg
       .Show
   End With
   
   End Function

2. If you have a small Bitmap File (bmp) somewhere on your machine, change the path in this line, logo = "{bmp D:\Images\CoLogo.bmp}", to the .bmp image location.

3. See that you have linked the Microsoft Office Object Library File to your Database. If you are not sure how to do it, then do the following:

   • Press Alt+F11 to display the VBA Editing Window (or Tools -> Macro -> Visual Basic Editor), if it is not already visible.

   • Select References from the Tools Menu.

   • Find Microsoft Office 11.0 Object Library (or whatever version is available) in the Available List and put a check mark to select it.

   • Click OK to close the Dialog Box.

4. Click somewhere in the middle of the above VBA Code you have pasted into your Database.

5. Press the F5 Key to Run the Code. The above Message Box will appear, with Office Assistant displaying your Bitmap Image above the message text.

The Enhancement Makes the Difference.

One important point to note is that the actual data visibility within the message text is underlined and colored. It catches the user's eye quickly and a lot better than showing everything in one Color.

Note: Readers who have not yet learned how to use the Office Assistant with MS Access Message Boxes may read the following Post and copy the Programs presented there into your Library Database or Project. You can download a sample Database with the Code from there.

Message Box using the Office Assistant.

You may also go through the following Posts to learn more interesting ways to use the Office Assistant with Message Boxes:

1. Message Box with Options Menu
2. Office Assistant with Check-Box Menu
3. Selection of Office Assistant

The AppointmentDt_LostFocus() Event Procedure that displays the above Message Box is given below:

Private Sub AppointmentDt_LostFocus()
Dim msg As String

If Me![AppointmentDt].OldValue <> Me![AppointmentDt].Value Then
    msg = AsstLogo & vbCr & "Existing {ul 1}Appointment Date:{ul 0}{cf 252} " & Me![AppointmentDt].OldValue & "{cf 0}" & vbCr & vbCr
    msg = msg & "Replace with {ul 1}New Date:{ul 0}{cf 249} " &  Me![AppointmentDt].Value & "{cf 0}...?"

    If MsgYN(msg, "AppointmentDt_LostFocus()") = vbNo Then
        Me![AppointmentDt].Value = Me![AppointmentDt].OldValue
    End If
    Me.Refresh
End If

End Sub

Text Formatting Codes.

If you check the above message text formatted and stored in the String Variable msg. You can see that it uses certain code values within {} (curly brackets) in the Message String to format the Underline and text Color. The old and new values for the AppointmentDt field are joined with the message text to show them in color.

At the beginning of the message text, I used the word AsstLogo to add the Company Logo above the Message Text. I will explain this after we check the other codes and usage.

The MsgYN() User Defined Message Box Function was created with Yes and No Option Buttons with the Office Assistant, and we learned about other options earlier.

There are only two types of Codes used within curly brackets to format the message text:

{ul } stands for Underline
{cf } stands for Color Format and can use 16 different Color Numbers.

{ul 1} turns On the underline and {ul 0} turns it Off.

{cf 0} (Black Color) is used to change the color of the text to the normal message color.

Example-1: MsgOK "Hi {ul 1}Michael{ul 0}, Welcome to Tips and Tricks."

Result: Hi Michael, Welcome to Tips and Tricks.

Example-2: MsgOK "Hi {cf 250}{ul 1}Michael{ul 0}{cf 0}, Welcome to Tips and Tricks."

Result: Hi Michael, Welcome to Tips and Tricks.

You can copy the text string part of the above examples into the first program above and try them out to find out.

You can use any of the following 16 Color Numbers in the {cf } format string:

Color Codes:

• Black: 0
• Dark Red: 1
• Dark Green: 2
• Dark Yellow: 3
• Dark Blue: 4
• Dark Magenta: 5
• Dark Cyan: 6
• Light Gray: 7
• Medium Gray: 248
• Red: 249
• Green: 250
• Yellow: 251
• Blue: 252
• Magenta: 253
• Cyan: 254
• White: 255

The main challenge when formatting the message text is entering the Text formatting Codes within curly brackets correctly, as it is easy to make mistakes when matching the opening and closing brackets. If this happens, part of the message text or the inserted field values may not appear in the Message Box.

A simple solution is to define the color codes as Global Constants in a standard VBA module and use these constants wherever needed in the message text. You can declare the color values as constants in a standard module like this:

Declaring Global Constants.

Constant declaration Examples:

Public Const AsstUlOn as String = "{ul 1}"

Public Const AsstUlOff as String = "{ul 0}"

Public Const AsstGreen as String = "{cf 250}"

Public Const AsstBlack as String = "{cf 0}"

Then we can rewrite the Example 2 Code above.

Example-2:

MsgOK "Hi " & AsstGreen & AsstUlOn & "Michael" & AsstUlOff & AsstBlack & ", Welcome to Tips and Tricks. "

No curly brackets or color codes to memorize. If you know the color name, then you can format the message quickly, the way you want.

Valid Image Formats.

To display the Company Logo above the Message Text (or below if you like), you can use two types of Images: a Bitmap Image (bmp) or a Windows Meta File (wmf) Image.

Bitmap Image usage: {bmp ImagePath}

Windows Meta File usage: {WMF ImagePath sizing_factor}

The sizing_factor determines the width of the WMF file displayed and is omitted for Bitmap Files. You can use the WMF image without the sizing_factor value.

The complete Global Constant declarations for the above color values are provided below. You can copy them into a standard module in your Common Library Database to use across multiple applications on your network, or into a single database to test them before implementing them in other applications.

Text Formatting Constant Declarations

Public Const AsstLogo As String = "{bmp D:\Images\CoLogo}"
Public Const AsstUlon As String = "{ul 1}"
Public Const AsstUloff As String = "{ul 0}"
Public Const AsstBlack As String = "{cf 0}"
Public Const AsstDarkRed As String = "{cf 1}"
Public Const AsstDarkGreen As String = "{cf 2}"
Public Const AsstDarkYellow As String = "{cf 3}"
Public Const AsstDarkBlue As String = "{cf 4}"
Public Const AsstDarkMagenta As String = "{cf 5}"
Public Const AsstDarkCyan As String = "{cf 6}"
Public Const AsstLightGray As String = "{cf 7}"
Public Const AsstMediumGray As String = "{cf 248}"
Public Const AsstRed As String = "{cf 249}"
Public Const AsstGreen As String = "{cf 250}"
Public Const AsstYellow As String = "{cf 251}"
Public Const AsstBlue As String = "{cf 252}"
Public Const AsstMagenta As String = "{cf 253}"
Public Const AsstCyan As String = "{cf 254}"
Public Const AsstWhite As String = "{cf 255}"

You can modify the image path on the first line to point to a bitmap image stored in a network’s common folder accessible to all your application users. This allows MS Access Applications on the network to use the same image.

Network Server Location Mapping.

Use the Image Path Name in UNC format (like \\ServerName\FolderName\ImageName.bmp) so that if the Disk Drive mapping is changed to a different letter, like K: or J:, etc., the programs will not lose contact with the image on the Network Path.

Instead of adding the AsstLogo Constant along with the message text every time, like:

Msg = AsstLogo & vbCr & "Hi Michael,..." It is better to add it to the main program code so that it is used for messages before they are displayed. See the modified code segment in the main program given above:

Public Function MyAssistant()
With Assistant.NewBalloon
    .Icon = msoIconAlertQuery
    .Animation = msoAnimationGetAttentionMinor
    .Heading = "Appointment Date"
 .text = AsstLogo & vbCr & msg
    .Show
End With
End Function

Modify the above Code segment of the Main Program you have copied from the earlier Article: Message Box uses Office Assistant to use the Company Logo in all your Message Boxes.

Displaying Greetings on Special Occasions.

During special occasions, such as Christmas or Valentine's Day, you can replace the company logo with themed images to delight and surprise your application users.

Check the sample Message Boxes with Christmas Images:

Right-click to open the Large Image in a New Window.

Or the Image of the King of Pop Music:

Right-click to open the Large Image in a New Window.

Note: These features work only in Access 2003 or earlier versions.

Image Source: Britannica Encyclopedia.

• Filter by Character and Sort
• Animating Label on Search Success
• Form Background with Gradient Color
• MS Access and Reference Library
• InputBox and Simple Menus

Read More