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

Introduction - 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 whether they really want to replace the previous date, 20/07/2009, with the date 25/07/2009.

The field name is underlined, and the old and new values are displayed in different colors. Additionally, the company logo appears 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 the 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 the 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 is underlined and colored. It catches the user's eye 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 a 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 is 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 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 typing all the codes within curly brackets correctly, as it’s easy to make mistakes in balancing 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 all 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 it.

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 all your 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 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, 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: