MsgBox with Office Assistant

Introduction - Access 2003.

Two Message Box examples are shown above. The one on the left is the default message box style in MS Access. The one on the right—featuring the Office Cat image—is created using the Office Assistant in VBA. These customized message boxes use the built-in features of the Office Assistant to enhance user interaction.

You can change the animated character from the Tools menu. By adding a few VBA functions to your MS Access project, you can use this feature wherever needed in your applications.

To simplify usage, several frequently used message box styles have been implemented as user-defined functions that utilize the Office Assistant. These functions are designed for convenience and require only two parameters:

1. Message Text – the main message to display.

2. Title (optional) – the caption displayed in the title bar of the message box. If this is not specified, a default title will be used.

The Button Type and Icon Type (such as the question mark icon displayed in the top-left corner of the message box) are preset within each function, based on the message type. This allows you to quickly use these message boxes without having to specify additional options every time.

The following user-defined functions are available, along with their usage syntax:

MsgOK("Message Text","Title") - MessageBox with only OK Button

MsgYN("Message Text","Title") - MessageBox with Yes & No Buttons. Returned Value is vbYes or vbNo

MsgOKCL("Message Text","Title") - MessageBox with OK and Cancel Buttons. Returned Value is vbOK or vbCancel

The function names listed above indicate the type of command buttons that will appear in the message box, as well as the return values corresponding to the user's responses.

Essential Library Files

First, you must attach the Microsoft Office 9.0 Object Library (or the version that matches your installed Office) to your project. This is essential for enabling the use of Office Assistant features in your VBA programs. Additionally, you’ll need to add a few other important library references that are not linked to Access by default.

For a complete list of required library files and detailed instructions on how to attach them, please refer to my earlier article titled Command-Button Animation.

Once the necessary libraries are attached, copy and paste the following code into a global module in your project and save it.

Public Function MsgOK(ByVal strmsg As String, Optional ByVal strHeading As String) As Integer
    On Error resume next
 MsgOK = MsgBalun(strmsg, strHeading, msoButtonSetOK, msoAnimationGestureUp, msoIconAlertInfo) 
End Function

Public Function MsgOKCL(ByVal strmsg As String, Optional ByVal strHeading As String) As Integer
  On Error Resume Next
  MsgOKCL = MsgBalun(strmsg, strHeading, msoButtonSetOkCancel, msoAnimationWritingNotingSomething, msoIconAlertQuery)
End Function 

Public Function MsgYN(ByVal strmsg As String, Optional ByVal strHeading As String) As Integer
on error resume next
    MsgYN = MsgBalun(strmsg, strHeading, msoButtonSetYesNo, msoAnimationWritingNotingSomething, msoIconAlertQuery)
End Function

Private Function MsgBalun(ByVal strText As String, ByVal strTitle As String, ByVal lngButtons As Long, ByVal intAnimation, ByVal intIcon) As Integer '------------------------------------------------------------ 
'Author : a.p.r. pillai 
'Date   : September 2006 
'Rights : All Rights Reserved by www.msaccesstips.com
'------------------------------------------------------------ 
Dim lngx As Long, intVal As Integer, Balu As Balloon 
On Error GoTo MsgBaloons_Err  
With Assistant   
If .On = False Then     
    .On = True
   '.FileName = "OFFCAT.acs"
     .Animation = msoAnimationGetAttentionMinor
     .AssistWithHelp = True
     .GuessHelp = True
     .FeatureTips = False
     .Visible = True
End If 
End With
  Set Balu = Assistant.NewBalloon
 With Balu
     .Animation = intAnimation
     .Icon = intIcon
    .Heading = strTitle
    .Text = strText
    .BalloonType = msoBalloonTypeButtons
    .Button = lngButtons
  Select Case Balu.Show
        Case msoBalloonButtonOK
            MsgBalun = vbOK
        Case msoBalloonButtonCancel
           MsgBalun = vbCancel
        Case msoBalloonButtonYes
           MsgBalun = vbYes
        Case msoBalloonButtonNo
           MsgBalun = vbNo
 End Select
 End With
  Assistant.Visible = False

MsgBaloons_Exit: 
Exit Function  

MsgBaloons_Err: 
MsgBox Err.Description, , "MsgBaloons" 
Resume MsgBaloons_Exit 
End Function 

You can use these Functions without bothering about selecting the Button-Type, IconType, etc. that you normally need to give along with the Message Box Command, like:

vbYesNo+vbDefaultButton2+vbQuestion

Usage Example:

If MsgYN("Select Yes to Proceed, No to Cancel.","cmdProcess") = vbYes then
    Docmd.runmacro "Process" 
End if 

OR

The second Parameter Title is omitted in the second example.

If MsgYN("Select Yes to Proceed, No to Cancel.") = vbYes then
    Docmd.runmacro "Process"
End if 

Testing the Code

You can type any of the above commands in the Debug Window and press the Enter Key, like the sample given below, to test the commands before using them in your programs:

MsgOK "System is preparing to shut down", "cmdExit_Click"

OR

MsgOK "System is preparing to shut down"

The MsgBalun() Function is not directly used in programs.

Implement the procedures in your Project and try them out.

Download

Download Demo Database

• Create 3D Heading
• Shadow 3D Heading Style
• Border 2D Heading Style
• Border 3D Heading Style