8.2 Formatting the Interface for Freestanding Excel Applications


8.2 Formatting the Interface for Freestanding Excel Applications

This section sets out from the assumption that you want to design an Excel application that can be installed with everything it needs to run on other computers. Basically, there are three possibilities as to how such applications can 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 operating mechanisms into custom toolbars that are automatically displayed when the file is opened. The advantage: There is no sharp distinction between "normal" Excel elements and additional commands. The drawback: The toolbar requires additional real estate on the monitor. Furthermore, either the items are redundant, or else the user is compelled to move back and forth between the standard menus and toolbars and those specific to the application.

  • On startup the application activates its own custom menus and toolbars. The operating elements for the application 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 save new toolbars together with an Excel file (via ViewToolbarsCustomizeToolbarsAttach). However, unlike what obtained in Excel 5/7, you cannot save any changes 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 section shows how an additional menu can be inserted into the standard menu when an Excel 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 out all extensions to the standard menu by way of the numerous VBA instructions for creating new items and labels, and assigning event procedures to them. The principal way of doing this is described 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 Copy 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 "CommandBar-Copy" is attached to the file CommandBar-Copy.xls . In Workbook_Open the first menu of this toolbar is copied with Copy to the second- to-last place in the standard menu. The menu is made visible with Visible=True , while the underlying toolbar is made invisible with Visible=False . See Figure 8-8.

click to expand
Figure 8-8: 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 CommandBarControl   Set standardmenubar = Application.CommandBars("worksheet menu bar")   Set mycommandbar = Application.CommandBars("CommandBar-Copy")   mycommandbar.Visible = False   ' test whether menu already exists   For Each c In standardmenubar.Controls     If c.Caption = mycommandbar.Controls(1).Caption Then       c.Visible = True       Exit Sub     End If   Next   ' 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 the job of making the menu disappear when the focus is shifted to another file; when the workbook 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/deactivate 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 Sub 

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_BeforeClose  (Cancel As Boolean)   Dim standardmenubar As CommandBar   Dim mycommandbar As CommandBar   Dim c As CommandBarControl   Set standardmenubar = Application.CommandBars("worksheet menu bar")   Set mycommandbar = Application.CommandBars("CommandBar-Copy")   For Each c In standardmenubar.Controls     If c.Caption = mycommandbar.Controls(1).Caption Then       c.Delete     End If   Next   mycommandbar.Delete End Sub 
Note  

This example program deals only with the standard menu. If you wish to alter the menus of charts , you will have to append the additional instructions for CommandBars("Chart Menu Bar") and there copy and later delete the new menu.

Hiding and Unhiding Custom Toolbars

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" ' display toolbar Private Sub  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 Sub ' display toolbar Private Sub  Workbook_Activate()  Application.CommandBars("Commandbar-Auto").Visible = True End Sub ' hide toolbar Private Sub  Workbook_Deactivate()  On Error Resume Next   Application.CommandBars("Commandbar-Auto").Visible = False End 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), into which the contents of "CommandBar-New" are copied. As soon as the new menu bar is made visible, the standard menu bar disappears. At program termination both the toolbar and the menu bar are deleted, and the standard menu appears automatically.

click to expand
Figure 8-9: The example program has its own standard menu
 ' CommandBar-NewMenu.xls, "ThisWorkbook" Private Sub  Workbook_Open()  Dim cb As CommandBar, c As CommandBarControl   ' make toolbar invisible   Application.CommandBars("Commandbar-New").Visible = False   ' create new toolbar   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_BeforeClose  (Cancel As Boolean)   On Error Resume Next   ' delete toolbar   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 Workbook_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-NewMenu.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 Collection variable visibleCommandBars . All toolbars in this list are automatically made visible as soon as another worksheet is activated.

 Dim visibleCommandBars As New Collection Private Sub  Workbook_Activate()  Dim cb As CommandBar   ' make new menu bar visible   Application.CommandBars("NewMenu").Visible = True   ' turn off all toolbars   For Each cb In Application.CommandBars     If cb.Type = msoBarTypeNormal And cb.Visible = True Then       visibleCommandBars.Add cb, cb.Name       cb.Visible = False     End If   Next End Sub Private Sub  Workbook_Deactivate()  Dim cb As Object   On Error Resume Next   Application.CommandBars("NewMenu").Visible = False   ' make the toolbar visible again   For Each cb In visibleCommandBars     cb.Visible = True   Next   ' delete Collection list   Set visibleCommandBars = Nothing End Sub 



Definitive Guide to Excel VBA
Linux Application Development For The Enterprise (Charles River Media Programming)
ISBN: 1584502533
EAN: 2147483647
Year: 2002
Pages: 134

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net