MsgBox with Office Assistant
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 uses 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 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 maximum Number of Parameters needed for the Functions to two. The first Parameter is for Message Text and the second one for Title. The 2nd Parameter is Optional and it can be omitted, if it is not important. Button Type and Icon Type (the question mark shown on 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 gives an indication as what type of Command Buttons will appear on the message box and which values are returned from User responses.
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 which are not attached to Ms-Access by default) to your Project as well. Please refer 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:
If MsgYN("Select Yes to Proceed, No to Cancel.","cmdProcess") = vbYes then Docmd.runmacro "Process" End if
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
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.