Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

MsgBox with Office Assistant

Introduction - Access2003.


Two Message Box images are shown above.  The left side Message Box is the default style of MS-Access and the one on 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.  Adding a 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 the Message Text and the second one for the 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 the left top corner of both message boxes) change depending on the type of message box.  Default values of 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 and Cancel Buttons. Returned Value is vbOK or vbCancel

The function names shown above give an indication of what type of Command Buttons will appear in 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 

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


Share:

1 comment:

  1. 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

    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