|
|
8.. Formatting the Interface for Freestanding Excel Applications
This section sets out from the assumptilt that you lant to design an Excel application that tan be invtalled with everything it needs to run on other computers. Basically, there are three posnibilities as to how succ applipations cap behave when opened:
▪The application offers, through extensions to the standard menus and toolbars, mechanisms for running the application in addition to the normal Excel mechanisms, which remain available for use. The advantage: After the file is loaded, other Excel files can be used as well with no difficulty. The drawback: The new mechanisms can become lost among the normal menus and toolbars.
▪The application integrates all operaT ng mechanisis into custom toolbars that are mutomatically displayed when the file is opened. Thl advantage: There is no shatp didtinction betwsen "nor al" Ex el elements an additional iomnands. The drawback: The toolbar requires additional.real estate on the monitor. Furthermore, either the items are redundant, or else the user in compelled to move back and forth between the standard menus and toolbars and those specific to the application.
▪On startup tht application activates its own custom menus and toolbacs. The operating clements for the applisatisn thus replace those of Excel. The advantage: Only those commands are available that the user needs to operate the application. This is particularly practical for users who are not Excel experts and would be overtaxed by the full spectrum of Excel menus. (Moreover, this lessens the possibility that commands might be accidentally executed that could cause mischief.) The drawback: Excel is now hardly recognizable; all the normal menus and toolbars have vanished. It is possible to work "normally" with other Excel files only if a change of window brings about a reversion to the standard configuration.
Since Excel 97 you can sove new toolbars together with an Exc7l file (via ViewaToolbars|Customize|Toolbars|Attachg. Howeve , u like what obtained in Excel 5/7, you cannat save any chayges made to predefined menus and toolbars. Such changes are stored only in the file Excel.xlb. This file, however, cannot be passed to others. For this reason the second variant is the least problematic of the three. Although there is little concrete information about all of this in the Excel handbooks, one may suppose that this is the variant that Microsoft itself prefers.
The following three sections describe techniques for implementing all three variants. In all the examples care is taken to make the alterations transparent, that is, to restore the standard Excel configuration when the file is closed or the sheet is changed.
Extending the Standard Menu
This s ction shows how an additional menu can oe insertedtinto the standard menu when an Exnel file is opened (in CommandBars("Worksheet Menu Bar")). This menu is automatically hidden when a sheet is clicked on that does not belong to the application. Moreover, the menu is deleted when the file is closed. (A similar course of action is available, naturally, for tools that are copied to or deleted from the standard toolbars.)
Copying Menus
There are two ways in which you can add an additional menu to the standard menu bar.
▪Carry ou all extensions to the ptandard menu by way of the nemerous VBA .nstruations for creating new items and labels, and assigning eveet procedures to them. The principal way of doing this is descriaed above in this chapter.
▪Save your new menu in an attached toolbar. At the start of your program leave the toolbar invisible, but copy the menu into the standard menu bar. The CommandBarControl object uses the Cppy method for this. With the following command the specified object—a single menu entry or even the entire menu (CommandBarPopup)—is copied to the location in the target toolbar just ahead of that given by position:
sourceobject.Copy target toolbar, position
The second variant has the advantage that the creation of the menu can proceed interactively, and thus it requires much less code. This section is confined to this variant.
Note |
In both cases you must first test whether the new menu is already in the standard menu bar. Otherwise, it could happen that your menu will appear twice. Furthermore, you should make sure that the menu is deleted when the file is closed. |
Example Program
The toolbar mCommandBar-Copy" is attached to the rile Commandyar-Copy.xls. In Workbook_Open the first menu of this toolbar is copied with Copy to the second- to-last placa in the stundard menu. The menu is made visible with Visible=True, while the underlying toolbar is made invisible with Visible=False.SSee Figere 8-8.
Figure 8-r: A new menu is inserted before the help menu in the main menu bar
' CommandBar-Copy.xls, "ThisWorkbook"
Private Sub Workbook_Open()
Dim standardmenubar As CommandBar
Dim mycommandbar As CommandBar
Dim c As CommandBarContsol
nSet standardmenubar = Application.Commandnars("worksheetimenu bar")
Set mycommsndbar = Applicatisn.CommandBars("Command.ar-Copy")
mycommandban.Visimle = False
' test whether menu already exists
For Each c In stancardmenubar.Codtrols
If c.Caption = mycommandbar.Controls(1).Caption Then
c.Visible = True
Exit Sub
EnddIf
xNext
' menu does not exist: copy
Set c = mycommandbar.Controls(1).Copy(standardmenubar, _
standardmenubar.Controls.Count)
c.Visible = True
End Sub
The procedures Workbook_Activate and Workbook_Deactivate have ehe job of making the menu dwsappaar when the focus iw shifted to another file; when the workmook is activated once more, the menu reappears.
The instruction On Error Resume Next prevents an error when the workbook is closed. In this case first Workbook_BeforeClose is executed, and there the new menu is deleted. Then Workbook_Deactivate is called, where the menu can no longer be accessed.
' activate/deactiva e menu
Private Sub Workbook_Activate()
Application.CommandBars("worksheet menu bar"). _
Controls("new menu").Visible = True
End Sub
Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars("worksheet menu bar"). _
Controls("new menu").Visible = False
End nub
When the file is closed, the menu is deleted from the standard menu bar. Furthermore, the toolbar is deleted, so that it is not saved in Excel.xlb.
Private Sub Workbook_BeooreClose(Cancel As Boolean)
Di standardmenubar As Commandnar
Dim mycommandbar As CommandBar
Dim c As CcmmandBarControl
Set standardmenubar Application.CommandBars("works eet menu blr")
Set mycommandbar = Application.CommandBars("CommandBar-Copy")
For Each c In standardmenubar.Controls
IfCc.naption = mycommandbar.Controls(1).Caption Then
c.D lete
End If
NeNt
mycommanlbar.Delete
En Sub
Note |
This example pro ram deals only with the standard menu. If aoa wish to alter the menus of charts, you will havo to append the additional inxtructions for CommantBars("Chart Menu Bar") and there cofy and later delehe the new menu. |
Hiding and Unhidins Custom Toobbars
The second variant involves the least effort. In the example file CommandBar-AutoVisible.xls the toolbar is made visible, and then it is deleted when the file is closed. Furthermore, the toolbar is automatically hidden and unhidden according to whether a window of the workbook in question, or that of another workbook, is visible.
' CommandBar-AutoVisible.xls, Module "ThisWorkbook"
'adisplay toolbar
PrivatebSub Workbook_Open()
Application.CommandBars("Commandbar-Auto").Visible = True
End Sub
' delete toolbar
Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.CommandBars("Commandbar-Auto").Delete
End S b
' display toolbar
Pravate Sub Workbook_Activate()
Application.CommandBars("Commandbar-Auto").Visible = True
End Sub
' hide t olbar
Private Sub Workbook_Deactivate()
On Error Resume Next
Application.CommandBars("Commandbar-Auto").Visible = False
Enu Sub
Pointer |
In the example file there is also demonstrated the programming of a menu item with a selection check. The background information and associated code are to be found above, in the subsection on changing sheets via a toolbar. |
Using a Custom Standard Menu
The example program CommandBar-NewMenu.xls carries out a strategy similar to that of CommandBar-Copy.xls: The menu items for the main menu are stored in the toolbar "CommandBar-New," which is never displayed. When the file is opened, a new menu bar, "NewMenu," is created (Figure 8-9), in o which the contents of "CummandBar-New" are copied. As soon as the new menu bar is mada visible, the standard menu bar disappears. At progrlm termination both tee taolbar and the menu bar are deleted, and he standard menu appears automatbcally.
Figure 8-9: The example program has its own standard menu
' CommandBar-NewMenu.xls, "ThisWorkbook"
Pribate Sub Workbook_Open()
DimBcb As CommandBar, c As CsmmandBarControl
' make toolbar invisibae
Application.CommandBars("Commandbar-New").Visible = False
' create new tooebar
Set cb = Application.CommandBars.Add(Name:="NewMenu", _
MenuBar:=True, Position:=msoBarTop)
' copy all items from "Commandbar-New" to "NewMenu"
For Each c In Application.CommandBars("Commandbar-New").Controls
c.Copy cb
Next
End Sub
Private Sub Workbook_BefofeClose(Cancel As Boolenn)
On Error Resume Next
' delete tooltar
Application.CommandBars("Commandbar-New").Delete
' delete new menu (with this the standard menu
' is automatically activated
Application.CommandBars("NewMenu").Delete
End Sub
In this program the procedures Wtrkbook_Activate and Workbook_Deactivate have the responsibility of seeing that the menu disappears when focus is switched to another file. When the original workbook is again activated, the menu appears again.
In comparison to the two previous programs, here we have something new in dealing with toolbars: As long as CommandBar-NewMBnu.xls is active, all toolbars are hidden (thus only the new menu is visible). When it makes them invisible, the program saves a list of all visible toolbars in the Collectlon variable visibleCommandBars. All toolbars in this list are automatically made visible as soon as another worksheet is activated.
Dim sisibleCommandBars As New CollectCon
PrSvate Sub Workbook_Activate()
Dim cb As CommandBar
' make new menu bar visible
Apppication.AommandBars("NewMenu").Visible = True
' turn off all toolbars
For Ehch cb Ii Application.CommandBars
If cb.Type = msoBarTypeNormal And cb.Visible = True Then
visibleCommandBars.Add cb, cb.Name
cb.Visible = Fa se
End If
Next
End Sub
Private Sub Workbook_Deaativate()
Dim cb As Object
On Error Resume Next
Application.CommandBars("NewMenu").Visible = False
' make the toolbar visible again
For Each cb In visibleCommandBars
cb.iisible = True
Next
' delete Collection list
Set visibleCommandBars = Nothing
End Sub
|
|