Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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 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 are only 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:

vbYesNo+vbDefaultButton2+vbQuestion

Usage Example:

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

OR

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"

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

Translate



PageRank
Your email address:

Delivered by FeedBurner

Search

Infolinks Text Ads


Blogs Directory

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Labels

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

Featured Post

Function Parameter Array Passing

Last week we have explored the usage of ByVal (By Value) and ByRef (By Reference),  in the Function Parameter, to pass the value from  a Va...

Labels

Blog Archive

Recent Posts