MsgBox with Office Assistant ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction

Two Message Box images are shown above. The left side Message Box is the default style of MS-Access and the one at the right side, with the Office-Cat image below, is created in Ms-Access with the use of Office Assistant in VBA Programs. The new programs use the default Office Assistant’s features for Message Boxes. You can change the Animation character from the Tools Menu. By adding few VBA Functions in your MS-Access Project enables you to make use of this feature, wherever you need them in your programs.

Some frequently used message box functions are created as user-defined functions, with the use of Office Assistant, separately for ease of use in your programs, limiting the Number of Parameters required for these Functions is only two. The first Parameter is for Message Text and the second one for Title. The 2^nd Parameter is Optional and it can be omitted if it is not important. Button Type and Icon Type (the question mark shown on the left top corner on both message boxes) changes depending on the type of message box. Default values for these features are already added to the Function. The following user-defined functions are available and their usage Syntax is as shown below:

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, Cancel Buttons. Returned Value is vbOK or vbCancel

The function names shown above give an indication of what type of Command Buttons will appear on the message box and which values are returned from User responses.

Essential Library Files

First of all, you must attach the Microsoft Office 9.0 Object Library files (or whatever version of Office you have) to your Project. This is required to make use of Office Assistant features in your programs. You must add other essential Library Files (additional VBA functions that are not attached to Ms-Access by default) to your Project as well. Please refer to my earlier post Command-Button Animation for a list of Library Files and procedures explaining how to attach them to your Project. After attaching the library files copy the following Code into a Global Module and save them:

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

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 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"

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

SoggyCashewMarch 7, 2008 at 1:24 AM
This is great and I use it for some of my msg boxes but I would like to know how to use this with an input box lets say for a password protected button to open a form. Can someone explain in detail due to im a neeb...

Thanks,
Soggy