Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Office Assistant And Msgbox Menus-3

After Clickable Menu Options - Access 2003.

After going through the earlier Articles on this subject I hope that the Readers are now familiar with programming the Balloon Object of Microsoft Office Assistant. You have seen that you can use this feature with a few lines of customizable VBA Code to display MsgBox text formatted with Color, underline, and with your favorite images on them. We can display Menus on them to obtain responses from Users, besides the buttons that we normally use like OK, Cancel, Yes, No, etc.

Since this article is the third part of this series I suggest that new Readers may go through the earlier Documents on this subject to learn interesting and simple ways to use this feature in MS-Access before continuing with this Article. Links to those Articles are given below:

Last week we learned how to display Clickable Menu Options in Message Box with the use of Office Assistant. The Image of that example is given below.

We have displayed the Menu Options in the Message Box with the Labels Property of the Balloon Object of Office Assistant.

Check Box Menu Options

Here, we will learn how to display Menu Options with Checkboxes and how responses from the User can be obtained, examined, and execute actions that are programmed for each choice made. The example code and the sample image of MsgBox that displays the Check-Box Menu is given below:

Public Function ChoicesCheckBox()
Dim i As Long, msg As String
Dim bln As Balloon, j As Integer
Dim selected As Integer, checked As Integer

Set bln = Assistant.NewBalloon
With bln
    .Heading = "Select Data Output Option"
    .Checkboxes(1).text = "Print Preview."
    .Checkboxes(2).text = "Export to Excel."
    .Checkboxes(3).text = "Datasheet View."
    .Button = msoButtonSetOkCancel
    .text = "Select one of " & .Checkboxes.Count & " Choices?"
    i = .Show

    selected = 0
    If i = msoBalloonButtonOK Then
        'Validate Selection
        For j = 1 To 3
            If .Checkboxes(j).checked = True Then
                selected = selected + 1
                checked = j
            End If

       'If User selected more than one item
        'then re-run this program and force the
        'User to select only one item as suggested
        'in the message text.

        If selected = 0 or selected > 1 Then
           Call ChoicesCheckBox
            Select Case checked
                Case 1
                    Debug.Print .Checkboxes(checked).text
                Case 2
                    Debug.Print .Checkboxes(checked).text
                Case 3
                   Debug.Print .Checkboxes(checked).text
            End Select
        End If
    End If
End With

End Function

Like the Labels Property Array, the dimension of CheckBoxes also can be up to a maximum of five elements only.

In our earlier example, we have not used the OK or Cancel buttons along with the Labels based Menu because the Balloon Button (msoBalloonTypeButtons) based options were clickable and accepted the clicked item as a valid response and dismisses the Office Assistant on this action. The clicked item's index number is returned as the response value and it was easy to check this value and execute the action accordingly.

But in the case of Checkboxes, this is a little more complex because of the following reasons

  1. The CheckBoxes can be either in checked or in the unchecked state, which needs to be validated.
  2. The User may put check marks on one or more Options at the same time. If this cannot be allowed, then there must be a validation check and force the User to make the selection of one item only.
  3. If the User has the option of selecting more than one item then the program must be written to execute more than one action based on the combination of selections made.
  4. In either case, we have to inspect the checked or unchecked state of each element of the CheckBox Array to determine the validity of Menu selection.

Validating the Checked/Unchecked Items

In the example code given above the User can select only one item at a time.

  • In the validation check stage of the code, first, we are checking whether the User has clicked the OK Button or not.
  • If she did then in the next step we take a count of all check-marked items, in the Variable selected.
  • If the value in the Variable selected is zero (The User clicked the OK Button without selecting any option from the list) or selected more than one item then the Program is called again from within the ChoicesCheckBox() Function itself. This will refresh the Menu, removes the check marks, and display it again. This will force the user to make only one selection as suggested in the message or she can click Cancel Button.
  • In the next step, the action is programmed based on the selection made by the User.
  • If the User is allowed to put check marks on more than one item (depending on the purpose of the MessageBox-based Menu) then the validation check and the execution of actions can be different and the code must be written accordingly.

The methods which I have introduced to you and explained in these three Articles are good to learn the basics of this feature and easy to understand the usage of different Properties of Balloon Object of Office Assistant.

But, you will appreciate the fact that duplicating and customizing these Codes everywhere in your Programs for different needs is not advisable. This will increase the size of your database, no flexibility in usage of Code and it is not good programming practice either.

You may go through the Articles (links are given below) published earlier on this Subject that it shows, how to define Public Functions like MsgOK(), MsgYN(), MsgOKCL(), and others with the use of Office Assistant. It simplifies the usage of this feature, without duplicating the code, and can use them freely anywhere in your Programs like MS-Access MsgBox() Function.

The above Function Names themselves suggest what kind of Buttons will appear in the Message Box when they are called with the minimum Parameter Value of Message Text alone or Message Text and Title Values.

  1. Message Box using Office-Assistant
  2. Message Box with Options Menu
  3. Office Assistant with CheckBox Menu

A comparison of the above User Defined Function usage with the MsgBox is given below for reference. The underscore character in the text indicates the continuation of lines and should not be used when all values are placed on the same line.

MS-Access MsgBox() usage Office-Assistant-based User Defined Function usage
MsgBox "Welcome to Tips and Tricks"MsgOK "Welcome to Tips and Tricks"
X = MsgBox("Shut Down Application", vbQuestion+vbDefaultButton2+vbYesNo, _"cmdClose_Click()")X = MsgYN("Shut Down Application", _ "cmdClose_Click()")
X = MsgBox( "Click OK to Proceed or Cancel?", _ vbOKCancel+vbDefaultButton2+vbQuestion, _ "MonthEndProcess()")X = MsgOKCL("Click OK to Proceed or Cancel?", _


No comments:

Post a Comment

Comments subject to moderation before publishing.




Post Feed


Popular Posts

Blog Archive

Powered by Blogger.


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