Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Color and Picture in Message Box

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?

  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, then change the path in this line logo = "{bmp D:\Images\CoLogo.bmp}" of the Code to your .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 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.
  4. Click somewhere in the middle of the above VBA Code you have pasted into your Database.
  5. 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:

  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) 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:

Or the Image of King of Pop Music:

Note: This feature works in Access 2003 or earlier versions of Access only.

Image Source: Brittanica Encyclopedia.

Share:

4 comments:

  1. […] all'utente, una personalizzazione della messaggistica e qui di seguito ne puoi trovare alcune: LEARN MS-ACCESS TIPS AND TRICKS - Color and Picture in Message Box MS Access: Enhanced Message Box Replacement | Cypris' lookout Data Strategies - Downloads […]

    ReplyDelete
  2. code not working wat i ask wat u gv

    ReplyDelete
  3. This works only in Access 2003 Version.

    ReplyDelete

Comments subject to moderation before publishing.

PRESENTATION: ACCESS USER GROUPS (EUROPE)

Translate

PageRank

Post Feed


Search

Popular Posts

Blog Archive

Powered by Blogger.

Labels

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