Learn Microsoft Access Advanced Programming Techniques, Tips and Tricks.

Office Assistant And Msgbox Menus-3

After going through the earlier Articles on this subject I hope that the Readers are now familiar with programming the Balloon Object of Microsoft Office Assistant. You have seen that you can use this feature with few lines of customizable VBA Code to display MsgBox text formatted with Color, underline and with your favorite Images on them. We can display Menus on them to obtain responses from Users, besides the buttons that we normally use like OK, Cancel, Yes, No etc.

Since, this Article is the third part of this series I suggest that new Readers may go through the earlier Documents on this subject to learn interesting and simple ways to use this feature in MS-Access before continuing with this Article. Links to those Articles are given below:

Last week we have learned how to display Clickable Menu Options in Message Box with the use of Office Assistant. The Image of that example is given below.

We have displayed the Menu Options on Message Box with the Labels Property of the Balloon Object of Office Assistant.

Here, we will learn how to display Menu Options with Checkboxes and how responses from the User can be obtained, examined and execute actions that is programmed for each choice made? The example Code and the sample image of MsgBox that displays the Check-Box Menu are given below:

Public Function ChoicesCheckBox()
Dim i As Long, msg As String
Dim bln As Balloon, j As Integer
Dim selected As Integer, checked As Integer

Set bln = Assistant.NewBalloon
With bln
    .Heading = "Select Data Output Option"
    .Checkboxes(1).text = "Print Preview."
    .Checkboxes(2).text = "Export to Excel."
    .Checkboxes(3).text = "Datasheet View."
    .Button = msoButtonSetOkCancel
    .text = "Select one of " & .Checkboxes.Count & " Choices?"
    i = .Show

    selected = 0
    If i = msoBalloonButtonOK Then
        'Validate Selection
        For j = 1 To 3
            If .Checkboxes(j).checked = True Then
                selected = selected + 1
                checked = j
            End If
        Next

       'If User selected more than one item
        'then re-run this program and force the
        'User to select only one item as suggested
        'in the message text.

        If selected = 0 or selected > 1 Then
           Call ChoicesCheckBox
        Else
            Select Case checked
                Case 1
                    Debug.Print .Checkboxes(checked).text
                Case 2
                    Debug.Print .Checkboxes(checked).text
                Case 3
                   Debug.Print .Checkboxes(checked).text
            End Select
        End If
    End If
End With

End Function

Like the Labels Property Array the dimension of CheckBoxes also can be up to a maximum of five elements only.

In our earlier example we have not used the OK or Cancel buttons along with the Labels based Menu because the Balloon Button (msoBalloonTypeButtons) based options were clickable and accepts the clicked item as a valid response and dismisses the Office Assistant on this action. The clicked item's index number is returned as the response value and it was easy to check this value and execute the action accordingly.

But in the case of Check-Boxes this is little more complex because of the following reasons

  1. The Check Boxes can be either in checked or in unchecked state, which needs to be validated.
  2. The User may put check-marks on one or more Options at the same time. If this cannot be allowed then there must be a validation check and force the User to make selection of one item only.
  3. If the User has the option of selecting more than one item then program must be written to execute more than one action based on the combination of selections made.
  4. In either case we have to inspect the checked or un-checked state of each element of the CheckBoxes Array to determine the validity of Menu selection.

In the example code given above the User can select only one item at a time.

  • In the validation check stage of the code, first we are checking whether the User has clicked the OK Button or not.
  • If she did then in the next step we take a count of all check-marked items, in the Variable selected.
  • If the value in Variable selected is zero (User clicked OK Button without selecting any option from the list) or selected more than one item then the Program is called again from within the ChoicesCheckBox() Function itself. This will refresh the Menu, removes the check marks and display it again. This will force the User to make only one selection as suggested in the message or she can click Cancel Button.
  • In the next step the action is programmed based on the selection made by the User.
  • If the User is allowed to put check-marks on more than one item (depending on the purpose of the Message Box based Menu) then the validation check and the execution of actions can be different and the code must be written accordingly.

The methods which I have introduced to you and explained in these three Articles are good to learn the basics of this feature and easy to understand the usage of different Properties of Balloon Object of Office Assistant.

But, you will appreciate the fact that duplicating and customizing these Codes everywhere in your Programs for different needs is not advisable. This will increase the size of your database, no flexibility in usage of Code and it is not good programming practice either.

You may go through the Articles (links given below) published earlier on this Subject that shows how to define Public Functions like MsgOK(), MsgYN(), MsgOKCL() and others with the use of Office Assistant. It simplifies the usage of this feature, without duplicating the Code, and can use them freely anywhere in your Programs like MS-Access MsgBox() Function.

The above Function Names themselves suggests what kind of Buttons will appear in the Message Box when they are called with the minimum Parameter Value of Message Text alone or Message Text and Title Values.

  1. Message Box with Office Assistant
  2. Message Box with Options Menu
  3. Office Assistant with CheckBox Menu

A comparison of the above User Defined Function usage with the MsgBox is given below for references. The underscore character in the text indicates the continuation of lines and should not be used when all values are placed on the same line.

MS-Access MsgBox() usage Office Assistant based User Defined Function usage
MsgBox "Welcome to Tips and Tricks"MsgOK "Welcome to Tips and Tricks"
X = MsgBox("Shut Down Application", _ vbQuestion+vbDefaultButton2+vbYesNo, _"cmdClose_Click()")X = MsgYN("Shut Down Application", _ "cmdClose_Click()")
X = MsgBox( "Click OK to Proceed or Cancel?", _ vbOKCancel+vbDefaultButton2+vbQuestion, _ "MonthEndProcess()")X = MsgOKCL("Click OK to Proceed or Cancel?", _
"MonthEndProcess()")

Share:

No comments:

Post a Comment

Comments are subject to moderation before publishing.

Translate



PageRank
Subscribe in a reader
Your email address:

Delivered by FeedBurner

http://clicky.com/101199826
Blog Directory Visit blogadda.com to discover Indian blogs

Search

Popular Posts

Search This Blog

Blog Archive

Powered by Blogger.

Follow by Email

Labels

Forms How Tos Functions MS-Access Security Reports msaccess forms Animations msaccess animation Class Module Utilities msaccess controls Access and Internet MS-Access Scurity MS-Access and Internet Array Queries msaccess reports External Links msaccess tips Accesstips Downloads Objects Property Menus and Toolbars Controls Event MsaccessLinks Process Controls Art Work Collection Object WithEvents msaccess How Tos Combo Boxes Graph Charts List Boxes VBA msaccessQuery Command Buttons Command Button Data Dictionary Object Emails and Alerts Form Query RaiseEvent Report Calculation Custom Wizards DOS Commands Data Type ms-access functions msaccess functions msaccess graphs msaccess reporttricks Item Object Reference msaccessprocess security advanced Access Security Add Custom Functions Field Type Fields Form Instances Key Macros Menus SubForm Top Values Variables msaccess email msaccess menus progressmeter Access2007 Auto-Number Copy Expression Join Methods Microsoft Numbering System Records Recordset Security Split Table Time Difference Utility Workgroup Wrapper Classes database ms-access msaccess wizards reference text tutorial vba code Access2003 Accounting Year Action Animation Attachment Binary Numbers Bookmarks Budgeting ChDir Color Palette Conditional Formatting Data Filtering Defining Pages Diagram Disk Dynamic Lookup Error Handler Excel 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 function hyperlinks iSeries Date iif msaccess msaccess alerts pdf files restore switch toolbar updating upload

Featured Post

WithEvents and All Form Control Types

Introduction So far we have worked extensively with only one type of Control: the TextBoxes on Form, enabled with Events to Raise and captu...

Labels

Blog Archive

Recent Posts