Office 97 Shared Programmable Objects

Presented by Ken Getz

Ken Getz is a Senior Consultant with MCW Technologies, a Microsoft Solution Provider focusing on the Microsoft® Visual Basic® programming system and the Office and BackOffice® suites of products. He recently completed VBA Developer's Handbook (co-authored with Mike Gilbert), and Access 97 Developer's Handbook (co-authored with Paul Litwin and Mike Gilbert), both for Sybex, Inc.

E-mail: keng@mcwtech.com

What Does Office Give You?

As Microsoft Office matures, more and more of the components of the individual applications are actually shared between multiple Office applications. Office Web, Office Art, and other major components are meant to be shared among all the Office applications. In addition, Office provides a group of “internal” components to each of the applications, including the Office Assistant, CommandBars, and the FileSearch object. In addition to the visible interface of the objects, each of these objects provides a rich programming model, making it possible for you to use them in your own applications. This document will discuss each of these in some depth, focusing on methods you can use to incorporate their functionality into solutions you write.

References Are Everything

In order to use any of the shared Office components programmatically, your application will need to include a reference to the Office 97 type library. Adding this type library reference to your project allows you to use early binding (declaring objects of variable types unknown to the host application), use the Object Browser to view the exposed Automation structure of the object, and use the online Help files associated with the object.

To add the necessary reference to your project, follow these steps:

1. Open any module in design view.
   
   
2. Choose the Tools|References... menu item.
   
   
3. From the list of installed type libraries, choose “Microsoft Office 8.0 Object Library.” If this item doesn’t appear on your list, choose the Browse... button, and search for MSO97.DLL (although this item should appear on your list, if you’ve installed Office 97 correctly).
   
   
4. Choose the OK button, and you’re all set and ready to go.

FileSearch

Microsoft Access for Windows® 95 included the Microsoft Office File Open dialog, with all its features, including the advanced searching capabilities. Unfortunately, Microsoft Access for Widows 95 didn’t provide any way for you to use that functionality in your applications. Office 97 now provides a rich object model for its built-in FileSearch object. Your Microsoft Access 97–based applications (actually, applications written in any Office 97 component that supports Visual Basic for Applications) can take advantage of the object model and provide full-featured searching for files across local, network, and remote drives.

The dialog itself, shown in Figures 1 and 2, can be used in its simple or advanced mode. The same is true for the FileSearch object. It can be used in a simple mode, searching with simple criteria, or can be used in its advanced mode, allowing you to specify a collection of PropertyTest objects that correspond to multiple search criteria. In addition, if you’ve enabled the Microsoft Fast Find feature (installed for you, by default, in your Startup group when you install Microsoft Office 97), the FileSearch object will use the indexes that program creates, providing extremely quick searches. The following sections and tables will document how you can make use of this useful technology to find documents.

Figure 1. The simple Open dialog allows you to search on a few criteria.

Figure 2. The Advanced Find dialog allows you to search for specific values in multiple properties, combined in complex Boolean searches.

How Does It Work?

There is only a single FileSearch object exposed by the Office programmability layer. That is, there’s no collection of these objects, and the single object is precreated for you. You needn’t use the New keyword when working with the FileSearch object. You can use either of the two samples that follow to work with the FileSearch object:

Dim fs As FileSearch
Set fs = Application.FileSearch
With fs
...
End With
' or

With Application.FileSearch
...
End With

In its simplest mode, you’ll use the FileSearch object like this:

1. Supply a criteria on which to search (a file name or a file type, perhaps), using the FileName property, or the TextOrProperty property.
   
   
2. Use the Execute method of the FileSearch object to start the search.
   
   
3. The Execute method returns the number of matching files that it found. You can check that value to see if the search was successful.
   
   
4. Loop through the FilesFound collection, each element of which is a string containing the name of a found file, performing whatever action you need on each file.

For example, you might write a simple routine like the one shown in Listing 1. This procedure searches through the Windows directory for all files with INI as the file extension. If it finds any matches, it displays the list in the Debug Window.

' Listing 1: Use the FileSearch object to perform simple searches.
Sub SimpleSearch()
    Dim varItem As Variant
    With Application.FileSearch
        .FileName = "*.ini"
        .LookIn = "C:\WINDOWS"
        .SearchSubFolders = False
        .Execute
        For Each varItem In .FoundFiles
            Debug.Print varItem
        Next varItem
    End With
End Sub

To perform more complex searches, you can replace the first step with a series of additions (using the Add method) to the PropertyTests collection. Once you’ve set up the PropertyTests collection, the rest of the steps are identical.

To clear out your settings and start a new search, you can use the NewSearch method of the FileSearch object. This method only resets all the properties-it doesn’t actually perform a search.

Creating Simple Searches

The following sections will describe the properties you’ll use when creating simple searches. The properties will be cross-referenced with settings available in the File Open dialog, shown in Figure 1.

Specifying what to look for, and where

Use the LookIn property to specify the drive and/or path in which to search. This property is a string expression and corresponds to the Look In drop-down list in Figure 1. Specify the FileName property to indicate the name (or file specification) of the files to search for. This can be a specific file name or a file specification (using MS-DOS® wildcards, “*” and “?”).

For example, the following code fragment will search for all text files in the root directory of the C drive:

With Application.FileSearch
    .LookIn = "C:\"
    .FileName = "*.txt"
    .Execute
End With

Specifying how to look for files

You can use any of the following properties to help narrow down the search or to provide more information for the search engine when locating your files. Using these options, you can place very fine control over the files you need to find:

• TextOrProperty: Use this string expression to specify text to search for either in the body of the document or in the properties of the document. There’s no way to specify that the search should only look at one or the other. This option corresponds to the Text or Property combo box in Figure 1.
  
  
• MatchAllWordForms: Set this property to True to find matches on all word forms of the word entered in the TextOrProperty property. This property will only have an effect if you’ve installed and registered mswds_en.lex (the name will be different for various localized versions). If you used the Typical setup when installing Office 97, this option wasn’t installed. If you specify “talk” in the TextOrProperty property, setting this property to True will cause the search to find matches against “talk,” “talks,” “talking,” and “talked.” This option corresponds to the Match all word forms check box in Figure 2.
  
  
• MatchTextExactly: If set to True, limits the search to an exact match on the text entered in the TextOrProperty property. This option corresponds to the Match Case check box in Figure 2.
  
  
• SearchSubFolders: If set to True, the search will include not only the path specified in the LookIn property, but also any folders contained within the specified folder. This option corresponds to the Search Subfolders check box in Figure 2.
  
  
• FileType: Allows you to specify the file type to search for. You can specify one of the following values: msoFileTypeAllFiles, msoFileTypeBinders, msoFileTypeDatabases, msoFileTypeExcelWorkbooks, msoFileTypeOfficeFiles, msoFileTypePowerPointPresentations, msoFileTypeTemplates, msoFileTypeWordDocuments. This option corresponds to the Files of Type drop-down list in Figure 1. The default for this property is msoFileTypeOfficeFiles, so unless you specify a file type or a file specification in the FileName property, all you’ll get is Microsoft Office files.
  
  
• LastModified: Set the property to any one of the following constants, indicating a range for the last modification date: msoLastModifiedAnyTime, msoLastModifiedLastMonth, msoLastModifiedLastWeek, msoLastModifiedThisMonth, msoLastModifiedThisWeek, msoLastModifiedToday, msoLastModifiedYesterday. This option corresponds to the Last Modified drop-down list in Figure 1.

Executing the search

To run a search, use the Execute method of the FileSearch object. The Execute method accepts three parameters, all of which are optional:

intFilesFound = .Execute(SortBy, SortOrder, AlwaysAccurate)

The method returns the number of matching files it located. The following paragraphs describe the optional parameters:

• SortBy: Indicates the attribute on which to sort the returned collection of filenames. Use one of the following constants: msoSortbyFileName, msoSortbyFileType, msoSortbyLastModified, msoSortbySize.
  
  
• SortOrder: Indicates the sorting order. Use one of the two following constants: msoSortOrderAscending and msoSortOrderDescending.
  
  
• AlwaysAccurate: Ordinarily, the search uses only the saved indexes (if you’re using the Fast Find program) to perform its search. If you want the search to also look on your disks, so that the returned list is accurate (even if the saved indexes are not), set this parameter to True. The default, False, is faster, but may not find files added or changed since the index file was last updated.

For example, the following fragment will run the specified search, returning values sorted by the last modified date, in descending order, finding all matches whether or not they’re in the Fast Find index:

intFilesFound = .Execute _
 msoSortByLastModified, msoSortOrderDescending, True) 

Using all the simple features

To test out all the features mentioned in the preceding paragraphs, try out frmTestSimpleSearch in the sample database. (See Figure 3.) This form allows you to try out all the simple properties (and the parameters to the Execute method) on your own files, to see how they all work. To make the form as simple as possible, the list box on the right is limited to 2048 characters (it uses a semicolon-delimited list to supply a value for the RowSource property of the list box, and that’s limited to just 2048 characters). If you want to provide a real interface like this one, you’ll need to modify the code to either write the file list to a table, and fill the listbox from there, or use a list-filling callback function to supply the list’s values.

Figure 3. Use all the simple search properties on this sample form.

Using advanced search techniques

The FileSearch object also allows you to create a list of properties and values for those properties specifying the files to find. As shown in Figure 2, you can create a collection of PropertyTest objects, each containing a property name, a condition, and a value or two to check for. The following sections will explain how to use the PropertyTests collection to create very specific searches.

The PropertyTests collection

The PropertyTests collection of the FileSearch object allows you to specify exactly the properties, and the values of those properties, you’d like to find as you search for files. When you run its Execute method, the FileSearch object will apply all the tests contained in its PropertyTests collection to each file that meets the location criteria (LookIn and SearchSubFolders properties). You can mix the simple FileSearch properties mentioned earlier with the more complex PropertyTests items, although the behavior is undefined if you overlap conditions. (In our tests, it appears that specifying a FileName property takes priority over using the “File Name” PropertyTest item. This isn’t documented, however, and you’d do best not to count on the behavior.)

Adding items to the PropertyTests collection

To add items to the PropertyTests collection, use the Add method of the collection. This method accepts up to five parameters, but only the first two (Name and Condition) are required. The allowed values for the properties are interrelated, and Tables 1 through 8 list out the possible values for each. The general syntax for the Add method is as follows:

FileSearch.PropertyTests.Add( _
 Name, Condition, Value, SecondValue, Connector)

The following list discusses each of the parameters and its values:

• Name: a string value corresponding to one of the built-in properties shown in Table 1, or a user-defined property name. Table 1 also indicates which values from Table 2 are available for a given property name.
  
  
• Condition: a numeric value from Table 2, indicating the condition applied to the property supplied in the Name parameter. Some conditions require no parameters, others a single value (supplied in the Value parameter), and others two parameters (supplied in the Value and SecondValue parameters). Table 2 indicates how many parameters each condition requires.
  
  
• Value: a variant, supplying the required value (if necessary) for the condition specified in the Condition parameter.
  
  
• SecondValue: a variant, supplying the second parameter (if necessary) for the selected condition.
  
  
• Connector: a numeric value, one of msoConnectorAnd and msoConnectorOr, indicating how the current member of the PropertyTests collection connects with other members that are modifying the same property Name. For example, in Figure 2, the two conditions applied to the “Last Saved By” parameter would use the msoConnectorOr value for this parameter, in the second “Last Saved By” element of the collection.

Table 1: All the built-in Office document properties and their associated conditions.

Table 2: All the msoCondition constants, grouped by functionality.

Using the PropertyTests collection

The example shown in Figures 1 and 2 provides a perfect test case for the PropertyTests collection. To produce the same search, from code, you might run a procedure like the one shown in Listing 2, below.

' Listing 2: Use the items from Tables 1 and 2 to provide 
' complex search criteria.
Sub ComplexSearch()
    ' Perform complex search using the FileSearch object and
    ' its PropertyTests collection.
    Dim varFile As Variant
    With Application.FileSearch
        .NewSearch
        ' Choose your own path here, of course!
        .LookIn = "C:\"
        .SearchSubFolders = False
        .FileType = msoFileTypeDatabases
        With .PropertyTests
           .Add "Last Modified", _
            msoConditionAnytimeBetween, #1/1/96#, Date
           .Add "Author", msoConditionIncludes, "Ken"
           .Add "Subject", msoConditionIncludes, "Developer"
        End With
        .Execute
        If .FoundFiles.Count > 0 Then
            For Each varFile In .FoundFiles
                Debug.Print varFile
            Next varFile
        End If
    End With
End Sub

Given all this flexibility, you should be able to find any file, local or remote, given any simple or complex set of criteria. Remember that using the Microsoft Fast Find indexing program will speed up your searches, but you don’t need to use it in order to take advantage of the FileSearch object.

CommandBars

Microsoft Office provides a shared mechanism for creating menus and toolbars to all its products. Microsoft Access takes advantage of this technology, and all its menus and toolbars get their functionality from shared Office code. In order to provide a unified object model for toolbars and menu bars, the Office team has provided the new CommandBar object hierarchy. A CommandBar object can appear in many guises, most obviously as a standard menu or as a toolbar.

Although the menu bar incarnation of a CommandBar resembles a standard Windows-based menu, it actually is a very different beast. Therefore, the bad news is that any code you have that uses the Windows API to reference menus will fail in Office 97. Even code that calls into menus created with macros will no longer work-those menus are created through the CommandBar code, as well. The good news is, of course, that Microsoft Access programmers finally have complete control over their menus and toolbars. You can now create menus and toolbars programmatically, control their placement and docking characteristics, hide and show menu items whenever necessary, and much more.

This section will discuss techniques you can use in applications to manipulate the new CommandBar object model and will discuss ways you can replace existing code that uses the Windows API to manipulate existing menus. Along the way, you’ll find techniques for solving typical programming problems involving menus, whether or not they were originally based on Windows API tricks.

Of course, in order to provide coverage of the CommandBar object model, we have to make some assumptions. We assume:

• You’ve worked with Microsoft Access commandbars.
  
  
• You’ve created and modified toolbars and menus from the user-interface.
  
  
• You already have an understanding of objects, properties, and methods.

What’s the difference between menus and toolbars?

Not much! They’re actually stored the same way. The big difference is how they’re displayed. A menu bar contains only text items, although popup menus can contain text and graphic items. A toolbar, on the other hand, normally contains just graphic elements and no text. The line can blur, of course, as you mix the text and graphic elements on a given commandbar. This document will focus mainly on the menubar side of things, because you’re most likely to want to programmatically control the menus in your application. You can, of course, apply everything shown here to either menus or toolbars-they “program” just the same. Although a given application can only have a single “active” menu bar, you can display as many menu bars and toolbars as your screen can contain. You control whether an object displays as a menu bar or as a toolbar, where it appears, whether it’s docked, and which items on the menu bar/toolbar are visible and enabled.

What about those API calls?

No doubt about it: All code that uses the Windows API to manipulate menus will need to be rewritten for Office 97. This document will provide some pointers and examples of using CommandBars and their object model to replace your existing code. Rather than focusing on individual API calls, however, we’ll provide sections on handling the most common scenarios that caused developers to work outside the tools Microsoft Access provided natively and suggest methods for achieving the same results, using the CommandBar object model instead of API calls.

In addition, if you’ve used the Windows API to manipulate a form’s system menu (the menu hanging off the Control Box), you’re out of luck-there’s just no way to emulate that behavior in Microsoft Access 97. Because those menus are now CommandBar objects, the API won’t work. And because there’s no exposed way to retrieve information about that menu from Microsoft Access (unlike the rich CommandBar object model for menus and toolbars), there’s just no reasonable way to work with these menus.

What Kind of Objects Are There?

As you can see from Figure 4, there really are only two types of objects in the CommandBar object hierarchy: CommandBar objects, and CommandBarControl objects. Each CommandBar object contains a collection of CommandBarControl objects. The interesting part is that the CommandBarControl object can be one of several types, as shown in Table 3.

Figure 4. The CommandBar object model is quite simple. Working with it is not.

Of course, things aren’t this simple. Built-in CommandBar objects can contain control types other than the ones shown in Table 3. This table lists the control types that you can create on CommandBars yourself. As usual, what you can do programmatically is a version or so behind what the Microsoft programmers can do with the same objects. You may be able to create drop-down grids and use all the other interesting command bar widgets that Microsoft Access itself will use in a future version, but not now.

Table 3: Base and Derived Classes for CommandBars

CommandBars and CommandBarControls

Although the object model for CommandBars is simple, there are so many options and variations that the actual work becomes quite complex. Because there are so few object types, and the objects all contain other objects, the recursion can get quite tricky. On the other hand, CommandBars can be boiled down to a few statements:

• Each CommandBar object contains a collection of CommandBarControl objects (its Controls collection).
  
  
• Each CommandBarControl object within the collection of controls can be either a CommandBarComboBox, a CommandBarButton, or a CommandBarPopup.
  
  
• If the control is a CommandBarComboBox or CommandBarButton, it has its own set of properties and methods, but it cannot contain other controls.
  
  
• If the control is a CommandBarPopup, then it has a CommandBar property that refers to the CommandBar object that it contains. The menus hanging off of CommandBarPopup controls are called popup menus.

Examples in the sections that follow will demonstrate how to enumerate, modify, and create objects within these collections.

Working with the CommandBar Collection

CommandBar collections work just like collections of any other type of object. The only truly difficult issue is that a CommandBar object can contain a collection of CommandBarControl objects, each of which might contain a single CommandBar object itself, accessed through the CommandBar property of the control. The following simple procedure, from basEnumerate, lists all the members of the CommandBars collection to the Debug Window. You’ll see right away that very few of the available CommandBar objects are actually visible. Imagine the chaos that would ensue if all the CommandBars were visible all the time! Most likely, only the Menu Bar and Visual Basic CommandBar objects are visible when you run this code. Any CommandBar object with its Visible property set to True will be visible in Microsoft Access.

Sub ListAllCBRs()
    Dim cbr As CommandBar
    For Each cbr In CommandBars
        Debug.Print cbr.Name, cbr.Visible
    Next cbr
End Sub

Perusing controls on a CommandBar

Each CommandBar object contains a collection of controls, and you can enumerate the elements of this collection, as well. The example below, in Listing 3, prints a list of all the CommandBar objects and the caption and type of each of the controls on each CommandBar.

To make the output from the procedure a bit more useful, it accepts an optional Boolean parameter: The parameter allows you to control whether the procedure displays all the CommandBars or just the visible ones. If you want to see the list of visible CommandBars, either call it with no parameter or send it a True value (that’s the default). If you want to see them all, pass a False value for the parameter.

If you run DumpCBRs (Listing 3), you’ll see that most of the items on the Visual Basic CommandBar are type 1 (msoControlButton), although the first item is type 14 (msoControlSplitButtonMRUPopup). You can create your own items of type 1, but you won’t be able to create your own type 14s (at least, not in this version of Microsoft Access). Also note that all the items on the Menu Bar CommandBar are type 10 (msoControlPopup), as they should be. This is the type you’ll find for all popup menus.

' Listing 3: Display visible CommandBars
Sub DumpCBRs(Optional VisibleOnly As Boolean = True)
    Dim cbr As CommandBar
    Dim cbc As CommandBarControl
    
    For Each cbr In CommandBars
        If VisibleOnly Imp cbr.Visible Then
            Debug.Print cbr.Name
            For Each cbc In cbr.Controls
                If VisibleOnly Imp cbc.Visible Then
                    Debug.Print , cbc.Caption, cbc.Type
                End If
            Next cbc
        End If
    Next cbr
End Sub

Looking at all menu items

The final example procedure in this section, DumpAllMenus (Listing 4), prints a list of all the items on the Microsoft Access menus or on a specific popup menu. It displays each item’s caption and its menu ID. DumpAllMenus calls the DumpMenu procedure to list items on a particular popup menu. That procedure calls itself recursively if it finds a control whose type is CommandBarPopup on the current popup menu. (None of the Microsoft Access menus are nested more than two levels deep, but you could nest your own menus deeper than that, and this procedure would still work correctly.)

To call DumpAllMenus, you can pass no parameters (it’ll list out all the items on all the top-level popup menus and their nested menus). You can also pass the name of a top-level submenu (“File,” “Edit,” “Help,” etc.), and it’ll list out all the items on that particular popup menu.

' Listing 4: List all CommandBarControls
Sub DumpAllMenus(Optional TopMenu As String = "Menu Bar")
    Dim cbr As CommandBar
    Dim cbp As CommandBarPopup
    
    Set cbr = CommandBars("Menu Bar")
    If TopMenu <> "Menu Bar" Then
        Set cbp = cbr.Controls(TopMenu)
        Call DumpMenu(cbp, 1)
    Else
        For Each cbp In cbr.Controls
            Debug.Print cbp.Caption
            Call DumpMenu(cbp, 1)
        Next cbp
    End If
End Sub

Sub DumpMenu(cbp As CommandBarPopup, intLevel As Integer)
    Dim cbc As CommandBarControl
    Dim intI As Integer
    
    For Each cbc In cbp.CommandBar.Controls
        ' Insert enough spaces to indent according to the 
        ' level of recursion.
        For intI = 0 To intLevel
            Debug.Print "   ";
        Next intI
        Debug.Print cbc.Caption, cbc.Id
        If cbc.Type = msoControlPopup Then
            ' Call this routine recursively, to document
            ' the next lower level.
            Call DumpMenu(cbc.Control, intLevel + 1)
        End If
    Next cbc
End Sub

Referring to CommandBars and Their Items

Because there’s no simple way for you to know which items exist in any given CommandBar’s collection of items, you’ll need to take some special steps to obtain a reference to a specific object within the Microsoft Access hierarchy of CommandBars. This section will discuss the various techniques you can use in order to point at the object you need.

Referring to CommandBars

To make it easy for you to retrieve a reference to a specific top-level CommandBar object, you can refer to the object by its English name. Even though your application may be translated into a local language besides English, the English references will work (just as they do for the GetOption and SetOption methods that require string parameters). That is, the following code will work in any localized version of Microsoft Access:

Dim cbr As CommandBar
Set cbr = CommandBars("Menu Bar")

When you start Microsoft Access (or any Office application), the CommandBars collection contains all the built-in items, and the application hides and shows them as necessary. Therefore, you can’t figure out an item’s index by looking at a menu or toolbar: All the hidden items take up an index “slot” as well. Instead, you’ll most often refer to elements at the CommandBar level by name, and at lower levels using the techniques shown later in this document.

Referring to items other than CommandBars

Unfortunately, objects other than CommandBars don’t expose both their local name and their English name (only the local name), so you’ll need to take extra steps to find a particular object within a CommandBar. If you attempt to refer to items on a CommandBar by their English names, your code won’t work in any other localized version of Microsoft Access. If you don’t care about this issue, you can build long strings of references, working your way through the collections. For example, to obtain a reference to the Tools|Analyze|Table menu item, you could use code like this:

Dim cbc As CommandBarControl 
Set cbc = CommandBars("Menu Bar"). _
 Controls("Tools").CommandBar.Controls("Analyze"). _
 CommandBar.Controls("Table")

On the other hand, if you need to be able to find a specific control in any localized version of Microsoft Access, you’ll use the FindControl method. This method, of either the CommandBars collection or of a specific CommandBar object, makes it possible to find the exact CommandBarControl object you need.

The syntax for the FindControl method looks like this:

object.FindControl(Type, Id, Tag, Visible, Recursive)

For this method, all the arguments are optional, but you must at least supply one of Type, ID, or Tag. Table 4 describes each of the parameters.

Table 4: Parameters for the FindControl method of the CommandBars collection or a specific CommandBar object

Why is the FindControl method available to the entire CommandBars collection, and to specific CommandBar objects, as well? If you need to find a control, and you don’t know on which CommandBar the user has placed it, using something like the following fragment will search all the CommandBar objects until it finds a match:

CommandBars.FindControl Id:=123, Recursive:=True

If you just want to search a particular CommandBar object (the Menu Bar object, for example), then you’ll want to use code like this:

Dim cbrMenu as CommandBar
Dim cbcDebug as CommandBarControl 
Set cbrMenu = CommandBars("Menu Bar")
' Find the Debug Window menu item.
Set cbcDebug = cbrMenu.FindControl(Id:=560, Recursive:=True)

Tips to Get You Started

If you’ve gotten this far, you at least have a basic concept about what a CommandBar object is, and how menu items “hang” off of a CommandBar. This section contains a number of small tidbits, some with longer explanations, helping to get you further along in your exploration of CommandBars. Using CommandBars is such a large topic, and so rich with possibilities, there’s no way we can cover all the options here. This section contains all the information you need to get started, and studying the examples is a great way to begin on your own CommandBar projects!

Referring to CommandBarControl captions

You can use item names without the “&” (indicating the position of the hot key), and the names are not case-sensitive. In addition, the trailing “…” included on some menu items is significant-if it’s there on the menu, you must include it in your object reference. (This tip really applies only if you’re searching for the menu control by name. That’s not a good idea if your intent is to write an application that will work in multiple localized languages.)

Everything’s a control

The objects on a given CommandBar are all controls. Whether you’re displaying the CommandBar as a toolbar or a menu bar, most elements are command buttons. The difference between a toolbar and a menu is how the button is displayed! Toolbars can also display controls besides command buttons (drop-down lists, for example); menus cannot.

Divide and conquer

Divider lines in menus and spaces in toolbars aren’t counted as controls. To indicate to the CommandBar that you want a divider line before a control, set the control’s BeginGroup property to True. The divider/space has no effect on any item counts, nor does it affect indexes within collections.

For example, the following code will insert a divider line before the third item (“Get External Data”) on the command bar referred to by cbrPopup (the popup menu hanging off of the first control on the main menu bar, the File menu):

Sub StartGroup()
    Dim cbrMain as CommandBar
    Dim cbrPopup as CommandBar
    Set cbrMain = CommandBars("Menu Bar")
    Set cbrPopup = cbrMain.Controls(1).CommandBar
    cbrPopup.Controls(3).BeginGroup = True
End Sub

Identification, please

A built-in CommandBar item’s ID property indicates the action that control will take when it’s selected. Setting a user-created control’s ID property to match one of the built-in ID values will make the item look like a built-in control, but not act like it. That is, assigning your new CommandBarControl item the ID property of the Edit|Cut control will set the text and the picture to match the built-in control. This will not, however, cause your new item to perform the action associated with the Edit|Cut menu item. If you want to take an action, you’ll need to set the OnAction property to call a macro or a function that performs the action.

To make it easier for you to work with menu ID values, we’ve included the DumpIDs procedure, in basDumpIDs. This procedure writes all the ID property values to tblControlID, allowing you to find the exact ID value you need.

Using the OnAction property

Use the OnAction property of CommandBarControl objects to execute an action when you select the item. The property can contain a string expression resolving to either the name of a macro (supply just the macro name, as a string) or a function call (in the form “=FunctionName()”). In order to cause a menu item to be checked, or to create a two-state button, the control must have its OnAction property set to call something. (To see two-state buttons in action, check out the View menu when the Database Explorer is selected: the Large Icons, Small Icons, List, and Details items all use a two-state button to indicate the current setting.)

Creating your own menu items

To create your own item on a menu (or a toolbar), you’ll use the Add method of a CommandBar object. Once you’ve created the new CommandBarControl object, you’ll need to set its properties so it does what you need. You must, at least, set the Caption and OnAction properties, so the item has a caption and does something when you select it. You can also set any or all of the other CommandBarControl properties, described in the Object Browser and online Help.

For example, the code in Listing 5 adds a menu item with the caption “Minimize Window” to the File menu, right before the “Save” item. The menu item will call the function MinimizeIt when it’s selected. The code also sets the item’s Tag property to match its caption, so you’ll be able to use the FindControl method later, if you need to find the menu item again. To show that this works, the code also adds a “Maximize Window” menu item, using the FindControl method to find the previously added control and adding the new item right above it.

' Listing 5: Use CommandBarControls' OnAction property.
Sub AddMinimize()

    ' Demonstrate creating a new menu item.
    
    Dim cbr As CommandBar
    Dim cbc As CommandBarControl
    Dim cbcFile As CommandBarControl
    Dim cbcSave As CommandBarControl
    Dim cbcMinimize As CommandBarControl
    
    Set cbr = CommandBars("Menu Bar")
    
    ' Search for the top-level File menu.
    Set cbcFile = cbr.FindControl(Id:=30002, _
     Recursive:=False)
    
    ' Search for the "Save" item on the File menu.
    Set cbcSave = cbr.FindControl(Id:=3, Recursive:=True)
    
    If Not cbcFile Is Nothing Then
        If cbcSave Is Nothing Then
            ' Add the item to the end of the menu.
            Set cbc = cbcFile.CommandBar.Controls.Add( _
             msoControlButton)
        Else
            ' Add the item before the Save item.
            Set cbc = cbcFile.CommandBar.Controls.Add( _
             msoControlButton, Before:=cbcSave.Index)
        End If
        With cbc
            .Caption = "Minimize Window"
            .OnAction = "=MinimizeIt()"
            ' Set the Tag property, so you can
            ' get back to this item later using FindControl.
            .Tag = "Minimize Window"
        End With
    End If
    Set cbcMinimize = cbr.FindControl( _
     Tag:="Minimize Window", _
     Recursive:=True)
    If Not cbcMinimize Is Nothing Then
        Set cbc = cbcFile.CommandBar.Controls.Add( _
         msoControlButton, Before:=cbcMinimize.Index)
        With cbc
            .Caption = "Maximize Window"
            .OnAction = "=MaximizeIt()"
            ' Set the Tag property, so you can
            ' get back to this item later using FindControl.
            .Tag = "Maximize Window"
        End With
    End If
End Sub

Function MinimizeIt()
    DoCmd.RunCommand acCmdMinimize
End Function

Function MaximizeIt()
    DoCmd.RunCommand acCmdDocMaximize
End Function

Where am I?

From within the code called by the string in the OnAction property, you can use the ActionControl property of the CommandBars collection to retrieve a reference to the control that called the code. This makes it possible to take different actions, depending on the menu or toolbar item that was just selected. If you’ve placed information in the control’s Tag property, you can also use that in your code, once you’ve used the ActionControl property to find out just which control called the code. The example, later in the document, that shows how to group menu items will use this property in order to know which item was just selected.

Doing Things the CommandBar Way

Once you’ve got the “hang” of working with CommandBars, you’ll want to start using some advanced techniques to really get them to do what you need. If you’ve used calls to the Windows API in Microsoft Access version 2.0 or Microsoft Access for Windows 95, however, you’ll need to replace those calls with code that’ll operate correctly with the new object model. The following sections will provide details on making those replacements. Although there’s no way to provide a one-to-one correspondence between specific API calls and Visual Basic for Applications code, the sections will attempt to help in the conversion. The information presented here is not only useful for ex-API callers, however! These techniques are useful for anyone attempting to make CommandBars do their bidding.

Retrieving the number of items in a menu

Your application may need to know the number of items on a menu. If your code is calling the GetMenuItemCount API function, you might try replacing it with simple code that retrieves the Count property of a given CommandBar. This won’t work, however, because the Count property also includes all the items that aren’t visible in the current context. You can use something like the following function to replace calls to the GetMenuItemCount API function:

Function GetMenuItemCount(cbr As CommandBar, _
 Optional ByVal CountAll As Boolean = False) _
 As Integer
    
    ' Given a CommandBar reference, return the
    ' number of items it includes. Optionally, count
    ' all the items. The default is to just count visible
    ' items.
    
    ' In:
    '    cbr: a reference to an existing CommandBar
    '    CountAll: (optional) True/False, indicating
    '      whether to include all menu items, or
    '      (default) only visible items.
    ' Out:
    '    Return value: the count of items, either visible,
    '      or all, depending on the value of the CountAll
    '      parameter.
    
    Dim cbc As CommandBarControl
    Dim intCount As Integer
    Dim fCountAll As Boolean
    
    For Each cbc In cbr.Controls
        ' Increment the count if either
        ' you're counting all, or this item
        ' actually is visible.
        If CountAll Or cbc.Visible Then
            intCount = intCount + 1
        End If
    Next cbc
    GetMenuItemCount = intCount
End Function

Disabling/enabling a menu item

Disabling and enabling (often called “greying”) a menu item is as simple as changing the Enabled property of a control. Once you’ve got a reference to the item you want to enable or disable, set the value of its Enabled property, and you’re all set. Using the Windows API, this required you to retrieve a menu handle, and then call the EnableMenuItem API function.

To disable the Edit|Undo Typing menu item using CommandBars, you might write code like this:

Dim cbc As CommandBarControl
Set cbc = CommandBars("Menu Bar").FindControl( _
 Id:=128, Recursive:=True)
cbc.Enabled = False

Attempting to enable a menu item that Microsoft Access wants disabled will be a fruitless exercise. You’re perfectly welcome to disable an enabled menu item, and Microsoft Access will respect your changes. On the other hand, if you attempt to enable a menu item that Microsoft Access thinks should be disabled, your change will be discarded without triggering an error. For example, attempting to enable the Edit|Can’t Undo menu item will have no effect, as long as Microsoft Access thinks it ought to be disabled. You will be able to programmatically disable the Edit|Undo Typing item, however, even if Microsoft Access would present the item as enabled.

To try this out, open frmTestUndo, Click the “Disable Undo Command” button, modify some text, and then try to use the Edit|Undo menu item. It’ll be disabled, because the button (which calls the code shown above) disables it.

Checking/unchecking a menu item

From a CommandBarControl’s point of view, the “checked” state of a menu item is the same as the “selected” state for a two-state toolbar button. For example, in Form Design view, the Align Left, Center, and Align Right toolbar buttons could also be represented on a menu, with one of the three items being “checked.” Using the Windows API, this required retrieving a menu handle for the parent menu and then using the CheckMenuItem API function.

You won’t find a “Checked” property in the Object Browser or online Help, because it doesn’t exist. Instead, you’ll use the State property of a CommandBarControl object to control the checked condition. In order to work with the checked state, your control must follow the following rules.

Your selected control must:

• Either have no specific value set for its ID property or have a value set that doesn’t insert a picture to the left of the menu item. (You can also set the Style property so that the control doesn’t display the associated image.)
  
  
• Not be a built-in control.
  
  
• Call a macro or function from its OnAction property.

So, when can you control the State property of a CommandBarControl? If you’ve created the item and set it up to call a macro or function when selected, and haven’t set an image or explicitly hidden the image, then you will be able to set its State property.

For example, the code in Listing 6 (from basCreateColors) creates a new menu containing five colors. Each item on the menu calls the HandleColors function when you select it, and that function places a check mark next to the selected item and clears the check for all other items. (Of course, in real life, you’d want the HandleColors routine to also perform some action in response to the menu selection!)

' Listing 6: Use the ActionControl property to find out 
' which control triggered the OnAction code
Sub CreateColors()
  Dim cbp As CommandBarPopup
  Dim varColors As Variant
  Dim intI As Integer
    
  ' Set up the array of colors.
  varColors = Array("Blue", "Green", "Pink", _
   "Yellow", "White")
        
  ' Create the top-level menu.
  Set cbp = CommandBars("Menu Bar").Controls.Add( _
   msoControlPopup, Temporary:=True)
  cbp.Caption = "&Color"
    
  ' Loop through the array, adding one menu item for
  ' each element of the array.
  With cbp.CommandBar.Controls
    For intI = LBound(varColors) To UBound(varColors)
      With .Add(msoControlButton)
        .Caption = varColors(intI)
        .OnAction = "=HandleColors()"
      End With
    Next intI
  End With
End Sub

Public Function HandleColors()
  Dim strCaption As String
  Dim cbc As CommandBarControl
  Dim cbcItem As CommandBarControl
    
  ' Get the selected control, and store its caption.
  Set cbc = CommandBars.ActionControl
  strCaption = cbc.Caption
    
  ' Loop through all the controls in the CommandBar
  ' object that's the parent of the selected control.
  For Each cbcItem In cbc.Parent.Controls
    With cbcItem
      ' Check the selected item, uncheck all the rest.
      If .Caption = cbc.Caption Then
        .State = msoButtonDown
      Else
        .State = msoButtonUp
      End If
    End With
  Next cbcItem
End Function

Changing the text of a menu item

Using the Windows API, you would have called the ModifyMenu and DrawMenuBar functions in order to modify the text of a menu item (after you’d retrieved a handle to the parent menu). Using CommandBars, change the Caption property (and perhaps the ToolTipText, ShortCutText, and DescriptionText properties, as well) of any control.

For example, to modify the text of the Edit|Delete menu item, you could write code like this:

With CommandBars("Menu Bar"). _
 Controls("Edit").CommandBar.Controls("Delete")
    .Caption = "Remove"
    .ToolTipText = "Remove the selected item"
End With

Deleting a menu item

To delete a menu item, even a control on the main menu bar, use the Delete method of a CommandBarControl. (If the control is a CommandBarPopup control and has a menu hanging off it, the Delete method for the item will delete its child menu as well.) The Delete method replaces calls to the RemoveMenu API function.

Some final thoughts

This document touched but a tiny portion of the functionality provided by the CommandBar object model. If you want to work with Office menus, take the time to dig through the online Help topics and the lists of properties and methods in the Object Browser. You’ll find much more flexibility in the new objects than was ever possible using the Windows API directly, and the code you’ll write will be much cleaner. Yes, it’s true, you’ll need to modify all your code that uses the Windows API to manipulate menus, but the final outcome will be simpler to read, maintain, and modify.

The Office Assistant

What You’ll Want to Do with the Assistant

If you’re like most developers I know, the first thing you’ll want to do with the Assistant is turn it off! For developers, it’s a cute little oddity that wears thin quickly. No matter what you want to do, or what you need to know, if you’ve been working with Microsoft Access for a while developing applications, you can do it or find it quicker without the Assistant.

On the other hand, end users and beginners will most likely find this new addition to all the Office products to be a welcome “helper.” Because they can choose their own character, with its own personality, many end users will find this simple-appearing yet deceptively complex technology to be a worthwhile addition to the product.

All the Office 97 applications use the Assistant to provide help, tips, and guides as users work with the products. You can also use the Assistant in your own applications, borrowing the look and functionality of the standard Office 97 applications.

What You Can Do with the Assistant

You can use the Assistant in your own applications to do the same sorts of things that it does for the standard Office applications:

• Provide context-sensitive Help.
  
  
• Show specific animations, based on options the user has chosen, steps the user has taken, or any other whim of your imagination.
  
  
• Use the Assistant Balloon object to provide a powerful replacement for the Microsoft Access MsgBox.

This paper will discuss the final two options: animating the Assistant and creating balloons that will allow you to request input.

When Can You Use the Assistant?

The Assistant is available only to users who own a copy of Microsoft Office 97. If you’re a Microsoft Excel or Word developer, this isn’t an issue, because anyone using your add-in or product will, of course, own the product. If you’re a Microsoft Access or Visual Basic developer, however, this complicates issues. Applications distributed with the run-time version of Microsoft Access or as Visual Basic 4.0 (or later) executables don’t have the legal rights to distribute the Office DLLs and character files that would be necessary to make the Assistant available to your application.

Assistant Object Model

The Assistant object model is very simple, and Figure 5 shows it in its entirety. Note that a Balloon object can contain a collection of up to five BalloonCheckBox objects (in the CheckBoxes collection) and up to five BalloonLabel objects (in the Labels collection), but you’re not likely to use both at the same time.

Figure 5. The Assistant object model. Objects with a shadow represent objects and collections; those without represent single objects.

As you can see from Figure 5, the Assistant object can own a single Balloon object (that’s the yellow area with buttons, labels, and icons that looks like a cartoon balloon). The balloon can contain up to five checkboxes or up to five labels (labels are the little blue “lights” on balloons that you can click on). In addition, if you specify text for the Labels collection (the collection of BalloonLabel objects), you can display the text with the blue lights, numbers, or bullets, depending on the BalloonType property.

Working with the Assistant

What can you do with the Assistant? Even without working with Balloon objects (covered later in this document), you have many choices you can make about the activities and display of the Assistant. The following sections will detail some of the properties and methods of the Assistant that you’ll be able to take advantage of in your own applications.

Personality and neighborhood

The most important characteristic of the Assistant is its character. The specific animations and sounds associated with a given character are stored in files with the extension ACT, normally in a folder inside the folder where you installed Office 97. You’ll use the FileName property to retrieve and set the file name for the character you want displayed. Once you’ve selected a character, the Name property returns a user-friendly name for the character. (This is the name you see when you select your character from the Options dialog.)

Where are those characters?

If you want to supply users with a list of installed characters, you can use a technique like that used in frmAnimations, shown in Figure 6. This form gathers a list of all the installed actors and provides that list to the user. The form also demonstrates all the available animations and some of the other visible option settings. Sections later in this document will discuss other features demonstrated by this form.

Figure 6. frmAnimations allows you to select actors and specific animations.

The code in frmAnimations retrieves the path for all the actor files from the Registry, using the GetActorPath function. If you’re interested in learning how to retrieve arbitrary values from the registry, you might take a peek at that function. Once the code knows where the files live (*.act), it uses the Dir function to loop through all the files in the directory, setting the Assistant’s FileName property to each file in turn. While each actor is loaded, the code retrieves the Name property as well and then moves on to the next file. As it works, it builds up a semicolon-delimited list of file names and actor names and then stuffs that string into the RowSource property of the combo box on the form. Listing 7 shows the code from frmAnimations:

' Listing 7: Retrieve a complete list of all the installed actors
Private Sub GatherList(strPath As String)
    ' Add all the actor file names and friendly names 
    ' to the collection.
    Dim strFile As String
    Dim strName As String
    Dim strOut As String
    
    Dim strOldFile As String
    Dim fVisible As Boolean
    With Assistant
        ' The next line ought not be necessary, but
        ' test it on your own machine. You don't want
        ' to see the Assistant flashing through all the
        ' actors. Here, you don't see a thing.
        '.Visible = False
        strOldFile = .FileName
    
        strFile = Dir(strPath & "\*.ac*")
        Do While Len(strFile) > 0
            strOut = strOut & ";" & strFile
            .FileName = strFile
            strOut = strOut & ";" & .Name
            strFile = Dir
        Loop
        ' Put the file name back the way it was, and then
        ' force the Assistant to be visible.
        .FileName = strOldFile
        .Visible = True
        If Len(strOut) > 0 Then
            Me!lstActors.RowSource = Mid(strOut, 2)
        End If
    End With
End Sub

Besides the Name and FileName properties, you can also set other properties that affect the location and display of the Assistant. Table 5 lists and describes these properties.

Table 5: Properties that control the display of the Assistant.

Animations 'R’ us

The Assistant provides the Animation property, which allows you to control exactly which animation the character displays at any time. You can choose only from the supplied animations, and not all characters respond to all the animations. For a list of all the possible values for the Animation property, see the Object Browser (msoAnimationType), or check out tblAnimations in the sample database. The sample form, frmAnimations, allows you to try out all the various animation settings with each actor.

A little bit random

Some animation settings (msoAnimationGreeting, for example) contain more than one possible display. When you select a particular Animation property setting, there’s no way for you to control exactly which of the possible animations you’ll get-it’s a random choice. You just have to give up a little control, once in a while!

I’m dancing as fast as I can!

No matter how hard you try, you cannot convince the Assistant to display a series of animations strung together. Because the actor will only “act” in your computer’s idle time, so that it doesn’t get in the way of real work, there’s no way for it to take over and perform animations serially. If you supply a list of actions, one after the other, you’ll most likely only see the final animation in the list. You may be able to work around this by adding timing loops to your code, but don’t. It’s just not the point.

Try out the DanceTwerpDance procedure in basAssistant, shown in Listing 8. You’ll see that although the code requests a series of animations, only one of them will be visible, most likely the final one.

' Listing 8: You can ask for as many actions as you like, 
' but the Assistant will disregard all but the final one.
Sub DanceTwerpDance()
    ' Note that the Assistant can only handle animation
    ' in "down-time". In this case, only the final animation
    ' will have any visible effect.
    With Assistant
        .Visible = True
        .Animation = msoAnimationGestureLeft
        .Animation = msoAnimationGestureRight
        .Animation = msoAnimationGestureUp
        .Animation = msoAnimationGestureDown
        .Animation = msoAnimationCharacterSuccessMajor
    End With
End Sub

Handling all the options

Many of the Assistant properties correspond one-to-one with options available in its Options dialog. (Right-click on the Assistant, and choose Options.) All of the options shown in Table 6 contain Boolean values and are read/write.

Table 6: Assistant properties corresponding to the Options dialog.

Assistant methods

The Assistant object supplies three additional methods, as shown below:

• Help: The Help method pops up a Balloon filled with its guesses as to what you’re trying to do, with jumps to the appropriate Help topics.
  
  
• Move: The Move method allows you to move the Assistant window to any location on the screen. For example, Assistant.Move(0, 0) will move the Assistant window to the upper-left corner. (Unlike other location values, these values must be supplied in pixels, not twips.)
  
  
• ResetTips: The ResetTips method resets the display of built-in tips back to the first item, so users may see tips they’ve seen before.

Creating a new balloon

A silent Assistant wouldn’t do you much good, so Microsoft Access allows you to create Balloon objects that display text from the Assistant. To create a balloon, you’ll usually follow these steps:

1. Use the NewBalloon method of the Assistant object to create the new balloon. This balloon will be blank and invisible.
   
   
2. Set the Heading property to assign a heading.
   
   
3. Set the Text property to assign some text to the body of the balloon.
   
   
4. Specify text for controls (labels or checkboxes) as needed.
   
   
5. Use the Show method to display the balloon.
   
   
6. If you’re using a modeless balloon (see later sections for more information), use the Close method to dismiss the balloon.

At any given time, the Assistant can only display a single balloon object and has no mechanism for maintaining a collection of Balloon objects internally. You’ll use the NewBalloon method of the Assistant to return a reference to a new Balloon object, and you can use this Balloon object to display text, labels, checkboxes, etc., to the user.

For example, Listing 9 (from basAssistant) creates and displays a very simple balloon. (The following section, “Working with Balloons,” will have more information on creating and using balloons and their text.)

' Listing 9: A very simple Balloon object
Sub SimpleBalloon()
    Dim bln As Balloon
    Set bln = Assistant.NewBalloon
    With bln
        .Heading = "The Simplest Balloon!"
        .Text = _
         "This is the simplest balloon you'll ever see!"
        .Show
    End With
End Sub

Working with Balloons

Balloon objects allow you to display text, request information, and interact in many ways with your applications’ users. The following sections will discuss the properties and methods of Balloon objects that allow you to create useful dialogs using the Balloon object.

What can you put on a balloon?

A Balloon object can contain any or all of the following items:

• A heading (using the Heading property)
  
  
• A section of text (using the Text property)
  
  
• Up to five labels, buttons, or numbered items (using the predefined Labels collection)
  
  
• Up to five checkboxes (using the predefined CheckBoxes collection)
  
  
• A group of command buttons (using the Button property)

The example in Listing 9 showed how to create the Balloon object and assign its Heading and Text properties. Later examples will show how to use the other Balloon object properties.

Showing a balloon

Once you’ve set up your Balloon object as you’d like it to appear, use the Show method to make it visible. See the example in Listing 9, which sets up the Heading and Text properties and then displays the balloon.

Getting modal

You can display a Balloon object in one of three modalities: Modal, Modeless, or AutoDown. Table 7 lists the three modalities and issues involved with each.

Table 7: Available settings for a balloon’s Mode property.

You’re dismissed! (Using the button property)

Balloon objects require a dismissal method. That is, if there’s no way to make the balloon go away, Microsoft Access won’t even display it in the first place. If you display the Labels collection as bullet items, for example (see the next section for details), and don’t otherwise provide some means for getting rid of the balloon, you’ll never see the balloon at all.

Use the Button property of the Balloon object to indicate what combination of command buttons you’d like to see on the Balloon. Table 8 lists all the possible combinations. (See the msoButtonSetType enumeration in the Object Browser for complete details.)

Table 8: Possible button combinations for Balloon objects.

Just as with the MsgBox function in Visual Basic for Applications, the buttons don’t actually do anything. When you select one of the buttons, the Balloon either disappears or calls a function or macro, depending on the Mode property. If the Balloon is opened with the msoModeModeless setting, clicking a button calls the function or macro specified in the CallBack property. If it’s set to msoModeModal, clicking a button closes the balloon, and the return value of the Show method indicates which button you clicked.

Table 9 lists all the possible return values from the Show method. Note that if you’ve set the BalloonType property for the balloon to be msoBalloonTypeButtons, and you’ve supplied text for one or more of the Labels collection items (see the next section for more details), the Show method will return a number between 1 and 5, indicating which of the labels was selected.

Table 9: All the possible return values from the Show method of a balloon.

The procedure in Listing 10 demonstrates the use of buttons and checking their value. It’s from basAssistant in the sample database.

' Listing 10: Use the return value of the Show method, 
' for Modal balloons, to find out which button the user pressed.
Sub TestShow()
    ' Demonstrate the Show method.
    Dim intRetval As Integer
    Dim strText As String
    Dim fVisible As Boolean
    ' Using multiple balloons doesn't look good unless the
    ' Assistant is visible to begin with.
    fVisible = Assistant.Visible
    Assistant.Visible = True
    
    With Assistant.NewBalloon
        .Heading = "Using Buttons and the Show method"
        .Text = "Select a button, and see what happens!"
        .Button = msoButtonSetBackNextClose
        .Mode = msoModeModal
        intRetval = .Show
        Select Case intRetval
            Case msoBalloonButtonBack
                strText = "You chose Back!"
            Case msoBalloonButtonNext
                strText = "You chose Next!"
            Case msoBalloonButtonClose
                strText = "You chose Close!"
        End Select
    End With
    ' Now create a new balloon, with the new text.
    ' Make this balloon go away as soon as you click
    ' anywhere outside the balloon.
    With Assistant.NewBalloon
        .Heading = "What Did You Choose?"
        .Text = strText
        .Mode = msoModeAutoDown
        .Show
    End With
    ' Put the Assistant away, if he wasn't visible to
    ' begin with.
    Assistant.Visible = fVisible
End Sub

Using the labels collection

You can add up to five short paragraphs of text to a Balloon object in the Labels collection. Depending on the value on the BalloonType property, the paragraphs will appear as numbered items, bulleted items, or as items in an option group (you can select one, and only one, of the items in the collection). Table 10 lists the possible values for the BalloonType property (See msoBalloonType in the Object Browser).

Table 10: Possible values for the BalloonTypeProperty.

The example shown in Listing 11 (from basAssistant) creates a simple set of labels from which the user can choose a value. Call the TestGetLevel procedure from the Debug Window to test it out. Figure 7 shows the balloon in action.

' Listing 11: Use the Labels collection to display 
' text for the user.
Function GetLevel()
    ' Call a modal balloon and return the value of the 
    ' selected label on the balloon.
    Dim bln As Balloon
    Set bln = Assistant.NewBalloon
    With bln
        .Heading = "User Information"
        .Text = "Select your skill level:"
        .Labels(1).Text = "Beginner."
        .Labels(2).Text = "Advanced."
        .Labels(3).Text = "Skip this information."
        .Mode = msoModeModal
        .BalloonType = msoBalloonTypeButtons
        .Button = msoButtonSetNone
        GetLevel = .Show
    End With
End Function

Sub TestGetLevel()
    Select Case GetLevel()
        Case 1
            Debug.Print "A beginner!"
        Case 2
            Debug.Print "An advanced user!"
        Case 3
            Debug.Print "Who knows?"
        Case Else
            Debug.Print "Invalid data!"
    End Select
End Sub 

Figure 7. Testing out the Labels collection.

Controlling the icon

You can control the icon displayed on your Balloon, as well. Set the Icon property to be one of the constants listed in Table 11. (See the msoIconType enumeration in the Object Browser for more details.)

Table 11: Icon constants for Assistant balloons

The example shown in Listing 12 shows a balloon that uses an icon. Figure 8 shows the example as it’s running.

' Listing 12: Use the Icon property of a Balloon object 
' to show a particular icon.
Sub BadThing()
    ' This whole example is a bad idea.
    Dim fVisible As Boolean
    
    With Assistant
        fVisible = .Visible
        .Visible = True
        .Animation = msoAnimationCharacterSuccessMajor
        With .NewBalloon
            .Heading = "Terrible Disaster!"
            .Icon = msoIconTip
            .Text = "I need to format your hard drive."
            .Button = msoButtonSetOkCancel
            ' Because the Assistant is visible, 
            ' MsgBox will use it
            ' to do its work.
            If .Show = msoBalloonButtonCancel Then
                MsgBox "Sorry, it's too late for that!", _
                 vbExclamation, "Your Drive is Toast!"
            End If
        End With
        .Visible = fVisible
    End With
End Sub

Figure 8. The wrong animation, the wrong icon, and disasters don’t mix.

Using the CheckBoxes collection

Just as you can place up to five text paragraphs in the Labels collection, you can place up to five checkboxes on a balloon, as well. (Normally, you’ll use either labels or checkboxes, but not both.)

To set a checkbox’s caption, set its Text property. To determine whether it’s been selected, look at its Checked property. Listing 13 demonstrates using the Text property.

The example in Listing 13 demonstrates the simple use of the CheckBoxes collection. It doesn’t, however, allow you to find out which checks the user selected-that requires a modeless balloon and a callback function. See online Help for more information.

' Listing 13: A simple usage of checkboxes in a balloon
Sub ShowChecks()
    With Assistant.NewBalloon
        .Heading = "Your Menu"
        .Text = _
         "Choose the items you'd like included in your meal:"
        .Checkboxes(1).Text = "Appetizer"
        .Checkboxes(2).Text = "Salad"
        .Checkboxes(3).Text = "Soup"
        .Checkboxes(4).Text = "Main Course"
        .Checkboxes(5).Text = "Dessert"
        .Show
    End With
End Sub

Figure 9. Using checkboxes on a simple balloon.

Finishing Touches

Here are some additional thoughts on using balloons:

• Plan carefully: Plan your use of balloons carefully before you begin to add them to your application. Because of their limitations (limited number of checks or labels), you’ll need to design them very carefully.
  
  
• One Assistant, many balloons: If you want to use multiple balloons in a session, make sure the Assistant is visible first-otherwise, you’ll see the Assistant appear and disappear for each balloon.
  
  
• Respect the users’ configurations: If you use the Assistant, make sure you reset its visibility when you’re done. Most of the examples in this section follow this suggestion.
  
  
• No run-time distribution allowed: Remember that run-time applications cannot use this functionality. If you plan to ship an application to a mixed group of end users (some using the retail version, others using only the run-time version), you’ll need to provide forms or MsgBox calls to replace the balloons for run-time users.

Is There More?

That rounds out the introduction to the three shared Office components you can use in Microsoft Access 97. But are there more? Of course! Almost all of the Office 97 components can be controlled via Automation and expose rich object models to allow you to control them. Office Art, Microsoft Graph, the Image Editor, and other features of Office all allow you to control them programmatically. Once you’ve set up the appropriate reference to the type library using the Tools|References... menu, you can program to your heart’s content. Finding documentation, however, is another issue altogether! Visit Microsoft’s Web site (http://www.microsoft.com/) to find what documentation there is. As time goes on, more and more of the shared components will become documented, and the Web site is the place to look for the new information.

© 1997 Microsoft Corporation. All rights reserved.

The information contained in this document represents the current view of Microsoft Corporation on the issues discussed as of the date of publication. Because Microsoft must respond to changing market conditions, it should not be interpreted to be a commitment on the part of Microsoft, and Microsoft cannot guarantee the accuracy of any information presented after the date of publication.

This document is for informational purposes only. MICROSOFT MAKES NO WARRANTIES, EXPRESS OR IMPLIED, IN THIS SUMMARY.

Microsoft, MS-DOS, Visual Basic, and Windows are registered trademarks and BackOffice is a trademark of Microsoft Corporation.

Other product or company names mentioned herein may be the trademarks of their respective owners.

The content of this session was excerpted from the following book, with permission of the publisher:

Access 97 Developer's Handbook, by Paul Litwin, Ken Getz, and Mike Gilbert. © 1997 Sybex, Inc.