Office Assistant with Check Box Menu ~ LEARN MS-ACCESS TIPS AND TRICKS

Friday, February 1, 2008

Office Assistant with Check Box Menu

Introduction - Access 2003.

In an earlier article, we explored using the Office Assistant for message boxes and option menus.

When designing applications, it’s worth adding features that stand out—something more engaging than the standard command buttons or plain message boxes. While data processing remains the core, repeating the same design elements can become monotonous for both the developer and the user. This is why Windows and Microsoft Office continue to evolve, offering new features and improved interfaces to keep the experience appealing.

Check-box Type Menu.

Next, we’ll experiment with checkbox-style menu options in a message box, displayed through the Office Assistant. We’ll use the same sample form created for the earlier program. An image showing the program in action is provided below.

Click to Enlarge

If you haven’t tried the earlier example, read the article MsgBox with Options Menu and follow the preparation steps outlined there to ensure these programs run without errors.

Open the OptionCheck form (created in the previous example) in Design View.
Add a second Command Button to the form.
Select the new button, open the Property Sheet (View → Properties), and update:
- Name: cmdChk
- Caption: Get Checked
With the form still in Design View, open its VBA module (View → Code).
Copy and paste the code provided below into the module, then save the form.

Private Sub cmdchk_Click()
Dim OptionArray(1 To 5) As String
Dim i As Integer, msg As String, title As String

OptionArray(1) = "Display Report Source Data"
OptionArray(2) = "Print Preview"
OptionArray(3) = "Print"

msg = "Please Select an Option"title = "Report Options"

i = MsgGetChk(msg, title, OptionArray())

If i > 0 Then
    Me![optTxt] = i & " : " & OptionArray(i)
End If

Select Case i
    Case -2
       msg = "Cancelled"
       Me![optTxt] = i & " : Cancelled"
    Case 1
        'DoCmd.OpenForm "myForm", acNormal
        msg = "Display Report Source Data"
    Case 2
        'DoCmd.OpenReport "myReport", acViewPreview
        msg = "Print Preview"
    Case 3
        'DoCmd.OpenReport "myReport", acViewNormal
        msg = "Print"
End Select

        MsgOK "Selected: " & msg
End Sub

The Get Checked routine displays a Check Box–style options menu inside an Office Assistant–based message box. When the command button cmdChk is clicked, the routine calls the MsgGetChk() function, passing the prompt text, dialog title, and an array of available options.

The user can select one or more check boxes and confirm the choice. The selected items are returned as a string, which is then displayed in:

A message box showing the chosen options.
A text box on the form for reference.

By encapsulating this functionality in MsgGetChk()The same check box menu can be reused from any form or module by simply passing different text and option arrays.

The Get Checked routine is a modified version of the Balloon-Type Options example used earlier in the form module. The core logic remains the same, but two key adjustments have been made for this demonstration:

Fewer menu options – The OptionArray() variable now holds only three items, as the OK and Cancel buttons are provided by the main program.
Cancel button handling – A Select Case...End Select structure includes a check for the Cancel button. If the user clicks Cancel, the program exits immediately.

In a real-world implementation, you can safely remove Case 2 its following two statements—these are included here only to illustrate the Cancel-handling logic.

The Main VBA Program.

Copy and paste the following main program into a Global VBA Module in your Project and save it.

Public Function MsgGetChk(ByVal strText As String, ByVal strTitle As String, ByRef obj) As Integer
Dim X As Integer, i, c As Integer, k As Integer
Dim Bal As Balloon, vSelect As Integer

On Error GoTo MsgGetChk_Err

Set Bal = Assistant.NewBalloon
i = 0k = UBound(obj)
k = IIf(k > 5, 5, k)

For X = 1 To k
   If Len(obj(X)) > 0 Then
     i = i + 1
   End If
Next

ForceEntry:

With Bal
   .Animation = msoAnimationWritingNotingSomething
   .Icon = msoIconAlert
   .Heading = strTitle
   .Text = strText
   .BalloonType = msoBalloonTypeButtons
   For X = 1 To i
     .Checkboxes(X).Text = obj(X)
   Next
   .Button = msoButtonSetOkCancel
  vSelect = .Show

  If vSelect = -2 Then
        MsgGetChk = vSelect
        Exit Function
  End If

  c = 0
  For X = 1 To i
    If .Checkboxes(X).Checked Then
       MsgGetChk = X 
' get the item checked by the user
       c = c + 1 
'more than one item checked
       If c > 1 Then
          Exit For
       End If
     End If
  Next

  If c > 1 Then
     strText = "Select only one item. "
     GoTo ForceEntry
  End If

  If c = 0 Then
     strText = "Select one of the Options or Click Cancel! "
     GoTo ForceEntry
  End If

End With

Assistant.Visible = False

MsgGetChk_Exit:
Exit Function

MsgGetChk_Err:
MsgBox Err.Description, , "MsgGetChk"
Resume MsgGetChk_Exit
End Function

This main program is largely the same as the Options Menu example from our earlier demonstration, with a few key changes:

1. MsgGetOpt Function

The `MsgGetOpt()` function is responsible for displaying the Office Assistant with a list of options in the form of a menu. It takes an array of option text items, assigns them to labels or checkboxes, and then displays them through the Office Assistant interface. The function waits for the user to make a choice, either by clicking OK or Cancel, and then returns the index of the selected item.

This modular approach allows the same function to be reused with different sets of options, making it versatile for a variety of menu-based interactions in your VBA projects.

2. Get Checked Routine

The Get Checked routine is adapted from the Balloon Type Options program we created earlier. Two key changes were made for this example:

Reduced option count – The `OptionArray()` variable now holds only three items, as the OK and Cancel buttons are provided in the main program itself.

Cancel handling – A `Select Case` block now checks for the Cancel condition. If the user clicks Cancel, the program exits.

When adapting this method for your own project, you can remove `Case 2` the two lines beneath it. They are included here only to demonstrate handling of the Cancel event.

3. Main Program (OptionCheck Form)

The main program closely follows the structure of our earlier Options Menu example, with the following adjustments:

The assignment statement

vba
labels(X).Text = MaxArray5obj(X)

is replaced by

vba
.Checkboxes(X).Text = obj(X)

Additional logic ensures:
1. Single selection enforcement – If more than one box is checked, the user is prompted to select only one.
2. Mandatory selection – If OK is clicked without checking any option, the user is asked to select one, or click Cancel if they change their mind.

Before running, make sure the Office Assistant is visible (Help → Show the Office Assistant or press Alt+H, then Alt+O).
To choose the Office Cat or another character, right-click the Assistant, select Choose Assistant, and use Next or Back to cycle through the options. The animation type is 'msoAnimationWritingNotingSomething' set in the program, and while it works with all characters, it pairs especially well with the Office Cat.

To test:

Open the OptionCheck form in Normal View.
Click Get Checked.
The Office Assistant will appear with the checkboxes.
Try clicking OK with multiple selections or no selections to see the validation prompts in action.