Color and Picture in Message Box ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction - Access2003

Image of a Microsoft Access Message Box, with the use of Office Assistant, is shown below. The Message Box pops up after the User changes the Appointment Date field value with a new date and the Message Alert asks the User to reconfirm whether she really needs to replace the earlier Date 20/07/2009 with the new value 25/07/2009 or not.

The Field Name text is underlined and field values (old and new) are displayed in different colors. Company-Logo is displayed above the message text.

A Quick Demo

Want to find out quickly how this works?

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

If you have a small Bitmap File (bmp) somewhere on your machine, then change the path in this line logo = "{bmp D:\Images\CoLogo.bmp}" of the Code to your .bmp image location.
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 Tools Menu.
- Find Microsoft Office 11.0 Object Library (or whatever Version available) in the Available List and put a check-mark to select it.
- Click OK to close the Dialog Box.
Click somewhere in the middle of the above VBA Code you have pasted into your Database.
Press F5 Key to Run the Code. You will find the above Message Box, with Office Assistant displaying your Bitmap Image above the message text.

The Enhancement Makes the Difference.

One important point to note here is that the visibility of the actual data displayed within the message text with underline and Color. It catches the eye of the User quickly and looks 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 in your Library Database or Project. You can download a sample Database with the Code from there too.

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:

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) embedded in the Message String to format the Underline and Color of the text. 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 have 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 Function we have created for displaying Message Box with Yes and No Option Buttons with the Office Assistant and learned about other options in the earlier Articles mentioned above.

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 used with 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 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 may copy the text string part of the above examples into the first program given 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 only difficulty you may experience while formatting the message text is to type all the codes in curly brackets and it is likely that you may make errors in balancing the opening or closing brackets. In such cases, part of the message text or the field values added to it will not appear in the Message Box when displayed.

There is an easy way out of this problem. Define all the above Color Codes as Global Constants in VBA Standard Module and use the Constants wherever we need them in the message text. You can declare the Color Values as Constants in a Standard Module like the following:

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 re-write 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 it.

Valid Image Formats

To display Company Logo above the Message Text (or below if you like) you can use two types of Images, Bitmap Image (bmp) or 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 wmf Image without the sizing_factor Value as well.

The complete Global Constant declarations for the above Color values are given below. You may copy them into the Standard Module of your Common Library Database to use across your Applications on the Network or into one of your Databases to try them out before implementing it in your 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 may modify the Image Path on the first line to point it to the Bitmap Image on the Network's common folder accessible to your Application Users so that all your MS-Access Applications on the Network can use this 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,. . . " Is better if you add it to the main program code so that it is used automatically for all the messages before they are displayed. See the modified Code segment on 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 like Christmas or Valentine's Day, you can replace the Company Logo Image with other images suitable to these occasions to give surprises to your Application Users.

Check the sample Message Boxes with Christmas Images: