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

After Clickable Menu Options - Access 2003.

After reviewing the earlier articles on this topic, I hope readers are now familiar with programming the Balloon Object of the Microsoft Office Assistant. You have seen that with just a few lines of customizable VBA code, you can display message boxes with formatted text—using colors, underlines, and even your favorite images. These balloons can also include menus to capture user responses, in addition to standard buttons like OK, Cancel, Yes, and No.

Since this article is the third part of the series, I recommend that new readers refer to the previous articles to learn these interesting and simple techniques in MS-Access before continuing. Links to those articles are provided 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 a Balloon Object using Office Assistant. The Image of that example is given below.

We have displayed the Menu Options in the Message Box using the Labels Property of the Balloon Object.

Check Box Menu Options

In this section, we will learn how to display menu options with checkboxes in a balloon message box and how to capture and process user responses. You will also see how to execute specific actions based on the choices made. The example code and a sample image of the message box with the checkbox menu are provided 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 can also be up to a maximum of five elements only.

In our earlier example, we did not use the OK or Cancel buttons alongside the label-based menu because the Balloon Button (msoBalloonTypeButtons), options were directly clickable. Clicking an item both registered the selection and dismissed the Office Assistant. The index number of the clicked item was returned as the response value, making it straightforward to check the selection and execute the corresponding action.

But in the case of check boxes, this is a little more complex because of the following reasons:

The checkboxes can be either checked or unchecked, and their state must be validated.
The user may select one or multiple options simultaneously. If multiple selections are not allowed, a validation check should enforce the choice of only one item.
Conversely, if multiple selections are permitted, the program must handle and execute the corresponding actions for each selected option.
In either scenario, it is essential to inspect the checked or unchecked state of each element in the CheckBox array to determine the validity of the menu selection.

Validating the Checked/Unchecked Items

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

During the validation stage of the code, the first step is to check whether the user clicked the OK button. If she did, the program counts all the check-marked items and stores the total in the variable selected.
If selected is zero (the user clicked OK without selecting any option) or greater than one, the ChoicesCheckBox() function is called again. This refreshes the menu, clears any existing check marks, and displays it anew, forcing the user to make a valid selection of a single item or allowing her to click Cancel.
Once a valid selection is made, the related program executes the action corresponding to the user’s choice.
If multiple selections are permitted, the validation logic and action execution will differ, and the code must be written accordingly to handle all selected items.

The Balloon Object of the Office Assistant, introduced and explained in these articles, provides a solid foundation for learning the basics of this feature and understanding the use of its various properties.

However, duplicating and customizing this code across multiple programs for different needs is not advisable. Doing so increases the size of your database, reduces code flexibility, and is generally poor programming practice.

You may refer to the earlier published articles (links provided below), which demonstrate how to define public functions such as MsgOK(), MsgYN(), MsgOKCL(), and others using the Office Assistant. These functions simplify the use of this feature, allowing you to call them anywhere in your programs—just like the standard MsgBox() function—without duplicating code.

The function names themselves indicate the type of buttons that will appear in the message box when called, either with just a message text or with a message text and title.

For reference, a comparison between the above user-defined functions and the standard MsgBox() function is provided below. Note that the underscore ( _ ) character is used to indicate line continuation in the code. If you place all values on a single line, the underscore should not be used.

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()")