MsgBox with Options Menu ~ LEARN MS-ACCESS TIPS AND TRICKS

Introduction.

This article is a follow-up to my earlier post on using the MsgBox With the Office Assistant.
In this installment, we’ll explore how to leverage the Office Assistant to display a balloon-style menu with multiple options for the user to choose from.

When the user clicks on one of the presented options, our VBA code detects the selected value and executes the corresponding programmed action. This technique can be a fun and interactive way to enhance user engagement in your MS Access applications.

Below is a sample screenshot of the Balloon Menu displayed through the Office Assistant:

Click to Enlarge

Preparing for a Demo.

Design a simple form with a Text Box and a Command Button, as shown in the illustration above, to run the demo program.

Rename the Text Box
- Click on the Text Box and open the Property Sheet (View → Properties).
- Change the Name property value to OptTxt.
- This control will be used only for displaying the selected option text during the demo.
Rename the Command Button
- Click on the Command Button and open its Property Sheet.
- Change the Name property value to cmdOpt.
- Change the Caption property value to Get Option.
Add the VBA Code
- While the sample form is still in Design View, open the form’s VBA module (View → Code).
- Copy and paste the code provided below into the form’s module.
- Save the form with the name OptionCheck, or choose any other name you prefer.

Private Sub cmdOpt_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"
OptionArray(4) = "CANCEL"

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

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

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

Select Case i
    Case 1
      'DoCmd.OpenForm "DataForm", acNormal
       msg = "Display Report Source Data"
    Case 2
       'DoCmd.OpenReport "myReport", acViewPreview
        msg = "Print Preview"
    Case 3
       'DoCmd.OpenReport "myReport", acViewNormal
        msg = "Print"
    Case 4
        msg = "CANCEL"
End Select

    MsgOK "selected: " & msg

End Sub

The VBA Code

Let’s break down what happens in the above routine.

First, we define a String array variable OptionArray with a maximum of five elements, and then load it with values in four of those elements. The main program MsgGetOpt() (code provided below) supports a maximum of five options. Even if you assign more than five elements to OptionArray the statement:

vba
k = IIf(k > 5, 5, k)

will limit the number of options to five.

Next, we initialize the variables msg and Title With the option text and the dialog box title. We then call the MsgGetOpt() program, passing these variables, along with the OptionArray—by reference. This ensures that the option values are sent directly into the routine for display.

If you have already completed the MsgBox with Office Assistant and Command Button Animation examples from earlier articles, you may proceed directly to step 3. However, make sure that you copy and paste these VBA routines into the same project where the main programs were placed earlier. If required, you can copy the main program code again from the following pages.

Setup instructions:

Copy the main programs from the page MsgBox with Office Assistant into a Global Module in your project, then save the module.
Link the Microsoft Office Library to your project. The procedure for doing this is described on the Command Button Animation page, which also lists other essential library references.
Copy and paste the following MsgGetOpt() code into a VBA Global Module, and save it.

Public Function MsgGetOpt(ByVal strText As String, ByVal strTitle As String, ByRef MaxArray5obj) As Integer
Dim intVal As Integer, X As Integer, Bal As Balloon, k As Integer
On Error GoTo MsgGetOpt_Err

k = UBound(MaxArray5obj)
k = IIf(k > 5, 5, k)

With Assistant
  If .On = False Then
    .On = True    
   '.FileName = "OFFCAT.acs"
    .Animation = msoAnimationBeginSpeaking
    .AssistWithHelp = True
    .GuessHelp = True
    .FeatureTips = False
    .Visible = True
  End If
End With

Set Bal = Assistant.NewBalloon
With Bal
   .Animation = msoAnimationWritingNotingSomething
   .Icon = msoIconAlert
   .Heading = strTitle
   .Text = strText
   .BalloonType = msoBalloonTypeButtons
   For X = 1 To k
    If Len(MaxArray5obj(X)) > 0 Then
     .labels(X).Text = MaxArray5obj(X)
    End If
   Next
   .Button = msoButtonSetNone
    intVal = .Show
End With

Assistant.Visible = False
MsgGetOpt = intVal

Set Bal = Nothing

MsgGetOpt_Exit:
Exit Function

MsgGetOpt_Err:
Err.Clear
Resume MsgGetOpt_Exit
End Function

Understanding the `MsgGetOpt()` Routine.

The MsgGetOpt() Routine is the core program that displays a balloon-style menu using the Office Assistant and allows the user to select from up to five options.

Here’s how it works:

Limit on Options – Although you can pass more than five values in the OptionArray The routine automatically restricts the total to five using the line:
```
vba
k = IIf(k > 5, 5, k)
```
This ensures the display remains neat and fits within the Office Assistant’s balloon window.
Balloon Setup – The routine uses the msg Title values you pass to it to create the balloon message and set the window’s title bar text.
Loading the Options – Each element of the OptionArray (up to five) is assigned to the balloon’s menu items, making them clickable choices for the user.
Displaying the Menu – The Office Assistant appears with the configured balloon, presenting the options to the user in a visually friendly way.
Capturing the User’s Choice – When the user clicks one of the options, the routine detects the selection and returns its value, so your program can take the appropriate action.

By organizing this functionality into a reusable MsgGetOpt() routine, you can call it from any form or module simply by passing the required message text and option values.

To see it in action, open the Demo Form named OptionCheck and click its Command Button. The Office Assistant-based message box will appear, displaying the available options, just like the sample image shown at the top of this page.

When you click one of the options:

A standard MsgBox will appear, showing the text of the option you selected.
The same selected option text, along with its corresponding sequence number, will also be displayed in the form’s TextBox.
Internally, the user’s selection is returned into the variable i inside the cmdOpt_Click() subroutine.

A Select Case ... End Select structure then evaluates the value i to determine which option was chosen and executes the appropriate block of code for that selection.

This approach makes the process interactive, user-friendly, and easy to adapt for any set of options you want to present.

Next, we will look into the CheckBoxes with the Office Assistant.