Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

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:

Preparing for a Demo

  1. Design a simple Form with a Text Box and a Command Button as shown above to run the Demo Program.
  2. 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.
  3. 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.
  4. 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 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.

  1. Copy and Paste the main programs from the Page MsgBox with Office Assistant into a Global Module in your Project and save it.
  2. 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.
  3. 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
   .Button = msoButtonSetNone
    intVal = .Show
End With

Assistant.Visible = False
MsgGetOpt = intVal

Set Bal = Nothing

Exit Function

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.

Download Demo Database.


No comments:

Post a Comment

Comments subject to moderation before publishing.


Subscribe in a reader
Your email address:

Delivered by FeedBurner


Popular Posts

Blog Archive

Powered by Blogger.

Follow by Email


Forms Functions How Tos MS-Access Security Reports Class Module msaccess forms Animations msaccess animation Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array External Links Queries msaccess reports Downloads msaccess tips Accesstips Objects Collection Object Property Event Menus and Toolbars Controls MsaccessLinks Process Controls WithEvents Art Work VBA msaccess How Tos Combo Boxes Dictionary Object Graph Charts List Boxes Query msaccessQuery Calculation Command Buttons Form Report Command Button Data Emails and Alerts RaiseEvent Custom Functions Custom Wizards DOS Commands Data Type Object Reference ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Key msaccessprocess security advanced Access Security Add Auto-Number Field Type Fields Form Instances Macros Menus Recordset SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Copy Excel Expression Join Methods Microsoft Numbering System Records Security Split Table Time Difference Utility Workgroup Wrapper Classes database function ms-access msaccess wizards reference text tutorial vba code Access Emails and Alerts Access Fields Access How Tos Access Mail Merge Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Database Records Defining Pages Diagram Disk Dynamic Lookup Error Handler Export External Filter Formatting Groups Hexadecimal Numbers Import Labels List Logo Macro Mail Merge Main Form Memo Monitoring Octal Numbers Operating System Paste Primary-Key Product Rank Reading Remove Rich Text Sequence SetFocus Summary Tab-Page Tables Union Query User Users Water-Mark Word automatically commands hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload