Office Assistant And Msgbox Menus-3

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:

• Color and Picture in MsgBox


• Office Assistant and MsgBox Menus


• Office Assistant and MsgBox Menus-2

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.


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
        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 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 CheckBoxes Array to determine the validity of Menu selection.

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

---

Network and Report Page Setup-3
Network and Report Page Setup-2
Network and Report Page Setup
Filter by Character and Sort
Animating Label on Search Success