MsgBox with Options Menu
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:
- Design a simple Form with a Text Box 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
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 and Title variables are initialized with Option Text and calls 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 have 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 are 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 Check Boxes with Office Assistant.