Office Assistant And Msgbox Menus-3 ~ LEARN MS-ACCESS TIPS AND TRICKS

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:

Color and Picture in MsgBox -Access 2003.
Office Assistant and MsgBox Menus - Access 2003.
Office Assistant and MsgBox Menus-2 - Access 2003.

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
        Next

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

The CheckBoxes can be either in checked or in the unchecked state, which needs to be validated.
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.
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.
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.

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?", _ "MonthEndProcess()")