To
move a menu bar or toolbar from the workspace to a workbook
| C H A P T E R 8 | Microsoft Office 97/Visual Basic Programmer's Guide |
| Menus and Toolbars |
An essential part of creating a useful custom application is providing a simple and consistent way for the user to interact with your Visual Basic application. Menus and toolbars provide a quick, convenient, and widely accessible way to expose simple commands and options to the user. In Microsoft Office 97, menus and toolbars are easy to design and modify; Microsoft Access 97, Microsoft Excel 97, Microsoft Word 97, and Microsoft PowerPoint 97 all share the same basic customization interface — the Customize dialog box. Because all menus and toolbars are represented by the same type of object — the CommandBar object — they're easy to customize and control from Visual Basic, as well.
The information in this chapter covers the shared menu and toolbar customization features of Microsoft Access, Microsoft Excel, Microsoft Word, and Microsoft PowerPoint. For more information about customizing menus and toolbars in Microsoft Access, see Chapter 1 in Building Applications with Microsoft Access 97, which is available in Microsoft Access 97 and Microsoft Office 97, Developer Edition. An online version of that book is available in the Value Pack on CDROM in Microsoft Access 97 and Microsoft Office 97, Professional Edition.
Note Microsoft Outlook doesn't provide an interface for customizing menus and toolbars. Therefore, none of the information in this chapter about the Customize dialog box applies to Microsoft Outlook.
You can use one or more custom toolbars in an application. Create the toolbars you want, and then use the appropriate method to display your custom toolbars:
Note There is no need to create event procedures for the Activate and Deactivate events of the form to show and hide toolbars, as was required in previous versions of Microsoft Access. Setting the Toolbar property to a custom toolbar automatically hides the builtin Form View toolbar when your form is opened and hides your custom toolbar when a user closes the form or switches to another form.
Microsoft Excel
You can store custom menu bars and toolbars with the workspace or with the workbook. When you quit Microsoft Excel, the toolbars in the workspace are automatically saved in the file Username8.xlb (where Username is the current user's Windows 95 logon name). If the user isn't logged on, the file name is Excel8.xlb. The toolbars saved in a workbook are stored in the workbook file.
Workbooklevel menu bars and toolbars make it easier for you to create a polished user interface for a custom application (an addin, for instance) and to distribute custom toolbar buttons and their supporting procedures. If you're going to distribute a custom toolbar with a custom application, you should attach it to the workbook that contains that application so that the toolbar is stored in the same file as the application.
To
move a menu bar or toolbar from the workspace to a workbook
The Attach Toolbars dialog box is displayed.
The name of the menu bar or toolbar you copied appears in the Toolbars in workbook box.
You can delete the original workspacelevel menu bar or toolbar by clicking the Toolbars tab in the Customize dialog box, selecting the name of the menu bar or toolbar you want to delete, and then clicking Delete. If you don't delete the workspace version of the menu bar or toolbar, you can change it without affecting the version stored in the workbook. If you make changes to the workspace version of the menu bar or toolbar and would like to update the workbook version to match the current workspace version, you can copy the workspace version to the workbook again, thus replacing the previous workbook version.
After you've copied a menu bar or toolbar to a workbook, the menu bar or toolbar becomes available only after the user has opened that workbook. A workbook version of the menu bar or toolbar retains not only its name and contents, but also the code assignments for menu items or toolbar controls; the location, size, and shape of the menu bar or toolbar; its onscreen position; and whether it's visible or hidden.
You can also delete a workbook version of a menu bar or toolbar.
To
delete a workbook version of a menu bar or toolbar
The Attach Toolbars dialog box is displayed.
Note You cannot use Visual Basic to attach menu bars or toolbars to a workbook or delete them from a workbook.
When you open a workbook that contains one or more menu bars or toolbars, Microsoft Excel first determines whether a workspace menu bar or toolbar with that name already exists. If not, Microsoft Excel creates a new workspace menu bar or toolbar and copies the workbook version into it. This way, the you get a fresh copy of the menu bar or toolbar, which you can alter by hiding it or by copying items or controls to or from the workspacelevel copy. When you quit Microsoft Excel, changes made to this copy of the menu bar or toolbar are stored with the workspace file.
There's no way to rename a menu bar or toolbar, so when the workbook is reopened, the workspace already contains a menu bar or toolbar with the same name as the workbook version, and Microsoft Excel uses the workspace copy rather than reloading the workbook version. However, the procedures that support the menu items or toolbar buttons in the open workbook still run when the user clicks the corresponding item or control.
As a developer, you can design a menu bar or toolbar and then attach it to a workbook. When the user opens the workbook, the custom menu bar or toolbar is available. The user can then edit it and move items or controls from it to personal menu bars or toolbars, without affecting the copy stored in the workbook. The user's changed menu bars and toolbars are stored with the workspace file when he or she quits Microsoft Excel. When the user starts Microsoft Excel again, the edited menu bar or toolbar is available; clicking one of the developer's menu items or toolbar controls loads the workbook that contains the procedure attached to that item or control. To generate a fresh copy of the workbook menu bar or toolbar, the user can delete the edited copy.
Word stores custom menus and toolbars in templates, just as it does with macros. When you customize a menu or create a new toolbar, changes are stored by default in the Normal template and are available "globally" — that is, you can always display a custom toolbar stored in the Normal template, even if the active document is based on a different template. A toolbar stored in a template other than Normal is available under either of two circumstances: the template is attached to the active document, or the template is loaded as a global template (Tools menu, Templates and Addins command). When you store a toolbar in a document, you can display the toolbar only when the document itself is active.
If you're going to distribute a Visual Basic application with customized menus and toolbars, you should store your menu bars and toolbars in a custom template or in a document. Because every user has his or her own Normal template, your Visual Basic application shouldn't change the Normal template. It's also easier to remove custom menus and toolbars when the user quits your application if the customizations are in the template or document that contains your application. That is, when the user closes the document (if the document contains toolbars or the template it's attached to contains toolbars) or unloads your template, your custom toolbars are no longer available; only the builtin menus and toolbars or the user's custom toolbars remain.
If two custom toolbars with the same name are available at the same time (for example, if the Normal template and a loaded global template both have a toolbar named "Custom Tools"), both toolbars are listed in the Customize dialog box and can be displayed either separately or at the same time.
In Visual Basic, you can add, customize, or delete menu bars and toolbars in any document or template. However, because the CommandBars property applies only to the Application object, you must set the context for your change before you make the change. Similar to using the Store in box on the Commands tab in the Customize dialog box, you can use the CustomizationContext property in Visual Basic to specify a Document or Template object that represents the document or template in which you want to make changes. You must set the CustomizationContext property before using the CommandBars property; this ensures that a reference to the collection of menu bars and toolbars for that document or template is returned. For more information, see "DesignTime Modifications to Toolbars" later in this chapter.
Custom menu bars and toolbars are always stored with the workspace. When you quit PowerPoint, the toolbars in the workspace are saved in the file Username.pcb (where Username is the current user's Windows 95 logon name). If the user isn't logged on, the file name is Powerpnt.pcb.
Because menu bars and toolbars aren't visible while a presentation is running, customizing menus and toolbars in PowerPoint is limited to changing the available menu commands and toolbar controls in design mode. You can use either the Customize dialog box or Visual Basic to modify your own design environment. If your Visual Basic application delivers a custom interface for designing presentations, you must use Visual Basic to make changes to menus and toolbars. When the user finishes with your application, it's a good idea for you to remove changes you made in Visual Basic.
Enabling or Disabling Toolbar Controls
You may want to control the availability of a toolbar control while your application is running, to prevent the user from clicking the button at inappropriate times. To do this, you can dynamically enable and disable the toolbar control. When a toolbar control is disabled, it beeps when it's clicked and doesn't run the procedure associated with it. Use the Enabled property to set or return the state (enabled or disabled) of a toolbar control.
The following example disables button three on the Standard
toolbar.
CommandBars("Standard").Controls(3).Enabled = False
Indicating the State of Toolbar Buttons
If a toolbar button represents an option with two possible states, you can change the appearance of the button to indicate the current state of the option: When the option is turned on, the associated button appears pushed down; when the option is turned off, the button appears flat.
The State property for a toolbar button is msoButtonDown
if the button appears pushed down; this property is msoButtonUp
if the button appears flat. The following procedure, which is
assigned to the new toolbar control Database View, changes
the appearance of the control before switching between special
views on the worksheet.
Sub DatabaseView()
With CommandBars("MyAppToolbar").Controls(3)
If .State = msoButtonDown Then
.State = msoButtonUp
'Switch to database view
Else
.State = msoButtonDown
'Switch to worksheet view
End If
End With
End Sub
Modifying Text Box, List Box, and Combo Box Controls
If you add custom text box, list box, or combo box controls to a toolbar, you can make runtime changes such as changing the current value of the text box portion of the control and adding or removing items from the list portion of the control (for list boxes and combo boxes only).
You can set the Text property of a text box, list box, or combo box control to reflect a change in the state of your Visual Basic application. For example, if the user clicks a toolbar button that runs a procedure named "MaxZoom" (a custom procedure that displays the active document at maximum zoom), the text box portion of a combo box control that's used to adjust the zoom more precisely and display a percentage value can be set to the maximum zoom percentage.
You can use the AddItem and RemoveItem methods to add and remove items (by index number) from the list portion of a list box or combo box control. For example, if you created a list box control in Word that tracks the styles the user applies during a session, you can add the name of a style to the list portion of the control each time the user applies a style.
Note Be careful when you add or remove an item in a list box or combo box control; this causes the index numbers of all the items to shift.
You can use other properties and methods of list box and combo box controls to change the appearance of a control at run time. For example, you can add and adjust a header list for the control (a header list is the group of list items at the top of the list portion of a control that are separated from the rest of the list items by a line.) For more information about using text box, list box, and combo box controls, see "Using command bars" in Help.
You can assign the ID of a builtin item to the Id argument of the Add method for the CommandBarControls collection, and you can assign the ID of a an item to the FaceId property of any custom or builtin control.
Note Even though the shared resource contains information about every menu item and toolbar control in all the Office applications, you can only add items and controls whose functionality is contained in the application you're working in. For example, you cannot add the Microsoft Excel Delete Rows toolbar button (ID 293) to a toolbar in Word. You can, however, copy the face of the Delete Rows toolbar button from Microsoft Excel to a toolbar control in Word.
To determine the IDs of the builtin menu items and toolbar controls in a specific Office application, you can do any of the following:
Sub outputIDs()
Const maxId = 4000
Open "c:\ids.txt" For Output As #1
' Create a temporary command bar with every
' available item and control assigned to it.
Set cbr = CommandBars.Add("Temporary", msoBarTop, False, True)
For i = 1 To maxId
On Error Resume Next
cbr.Controls.Add Id:=i
Next
On Error GoTo 0
' Write the ID and caption of each control to the output file.
For Each btn In cbr.Controls
Write #1, btn.Id, btn.Caption
Next
' Delete the command bar and close the output file.
cbr.Delete
Close #1
End Sub
Sub MakeAllFaceIds()
'Make fourteen toolbars with 300 faces each.
'Note that maxId is greater than last valid ID, so
'error will occur when first invalid ID is used.
Const maxId = 3900
On Error GoTo realMax
For bars = 0 To 13
firstId = bars * 300
lastId = firstId + 299
Set tb = CommandBars.Add
For i = firstId To lastId
Set btn = tb.Controls.Add
btn.FaceId = i
btn.TooltipText = "FaceId = " & i
Next
tb.Name = ("Faces " & CStr(firstId) & " to " _
& CStr(lastId))
tb.Width = 591
tb.Visible = True
Next
'Delete the button that caused the error and set toolbar name
realMax:
btn.Delete
tb.Name = ("Faces " & CStr(firstId) & " to " _
& CStr(i - 1))
tb.Width = 591
tb.Visible = True
End Sub
Note The
IDs of the popup controls for builtin menus are in
the range 30002 to 30426. Remember that these IDs return empty
copies of the builtin menus.