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

Introduction

This is an extension of my earlier Article on MsgBox with Office Assistant. Here, we will try to make use of Office Assistant to display a set of Menu Options and ask the User to select one of them. When the user clicks on one of the Options we will test for the selected value and execute the action programmed for it.

A demo image of the Baloon Type Menu Options displayed in Office Assistant is given below:

Click to Enlarge

Preparing for a Demo

Design a simple form with a TextBox and a Command Button as shown above to run the Demo Program.
Click on the Text Box and Display the Property Sheet (View - - > Properties) and change the Name property Value to OptTxt. This is used only for displaying the selected Option Text for Demo purposes.
Click on the Command Button and display its property sheet. Change the Name Property Value to cmdOpt and the Caption property Value to Get Option.
While the sample form is still in Design View, display the Form's VBA Module (View -> Code). Copy and paste the following Code into the Form's Module and save the Form with the name OptionCheck or with any 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 us look into what we are doing in the above Routine. First, we have defined a String Array Variable OptionArray with a maximum of 5 elements and loaded them with values in 4 elements out of 5 defined. A maximum of 5 elements or less are allowed in the main program MsgGetOpt() (the Code is given below). Even if you define more than 5 elements in the OptionArray it will be cut to the maximum of 5 elements by the statement k=iif(k>5,5,k) in the main program.

The msg & Title variables are initialized with Option Text and call the Program MsgGetOpt() with its parameters: msg, Title, and OptionArray variable, passing the OptionArray values by reference.

If you have already tried out the MsgBox with Office Assistant and Command Button Animation Topics earlier continue with step 3. But, Copy and Paste these VBA Codes on the same Project where you placed the Main Programs earlier. Or copy the Main Programs again from the following pages.

Copy and Paste the main programs from the Page MsgBox with Office Assistant into a Global Module in your Project and save it.
You must Link the 'Microsoft Office Library' File to your project as well. The procedure for doing that is explained on the Page Command Button Animation. A-List of other essential Library Files is also given there.
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

Open the Demo Form OptionCheck and click on the Command Button. You should see the Office Assistant-based MsgBox displaying the Options as shown in the sample image given on top of this page. Click on one of the Options. Another MsgBox will display the Option Text that you have selected. The selected option text with its corresponding sequence Number will appear in the TextBox on the Form as well. The User-selected option value is returned into the variable i in the cmdOpt_Click() Sub Routine. Performs a test on the variable i within the Select Case, End Select structure to find out which option the user has selected and executes the statements programmed there.

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