Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Office Assistant And Msgbox Menus-3

After Clickable Menu Options

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 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 have 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 on 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 is programmed for each choice made? The example Code and the sample image of MsgBox that displays the Check-Box Menu are 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 accepts 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 Check-Boxes this is little more complex because of the following reasons

  1. The Check Boxes can be either in checked or in 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 selection of one item only.
  3. If the User has the option of selecting more than one item then 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 un-checked 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 Variable selected is zero (User clicked 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 Message Box 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 given below) published earlier on this Subject that 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 suggests 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 with 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 references. 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.


Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email


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