Simulating a Toolbar with a UserForm


Creating a custom toolbar in versions prior to Excel 2007 was relatively easy. With Excel 2007, it's impossible . More accurately, it's still possible to create a custom toolbar with VBA, but Excel ignores many of your VBA instructions. In Excel 2007, all custom toolbars are displayed in the Add-Ins image from book Custom Toolbars Ribbon group . These toolbars cannot be moved, floated, resized, or docked .

This section describes how to create a toolbar alternative: A modeless UserForm that simulates a floating toolbar. Figure 15-16 shows a UserForm that may substitute for a toolbar.

image from book
Figure 15-16: A UserForm set up to function as a toolbar.
CD-ROM  

This example, named simulated toolbar.xlm , is available on the companion CD-ROM.

The UserForm contains eight Image controls, and each executes a macro. Figure 15-17 shows the UserForm in the VBE. Notice that:

  • The controls are not aligned.

  • The UserForm is not the final size .

  • The title bar is the standard size.

image from book
Figure 15-17: The UserForm that simulates a toolbar.

The VBA code takes care of the cosmetic details. It aligns the controls and adjusts the size of the UserForm so there's no wasted space. In addition, the code uses Windows API functions to make the UserForm's title bar smaller - just like a real toolbar. To make the UserForm look even more like a toolbar, I also set the ControlTipText property of each Image control - which displays a very toolbar-like tooltip when the mouse is hovered over the control.

If you open the file on the CD-ROM, you'll also notice that the images change slightly when the mouse is hovered over them. That's because each Image control has an associated MouseMove event handler that changes the SpecialEffect property. Here's the MouseMove event handler procedure for Image1 (the others are identical):

 Private Sub Image1_MouseMove(ByVal Button As Integer, _    ByVal Shift As Integer, ByVal  As Single, ByVal Y As Single)     Call NoRaise     Image1.SpecialEffect = fmSpecialEffectRaised End Sub 

This procedure calls the NoRaise procedure, which turns off the raised special effect for each control.

 Private Sub NoRaise() '   Remove the raised effect from all controls     Dim ctl As Control     For Each ctl In Controls         ctl.SpecialEffect = fmSpecialEffectFlat     Next ctl End Sub 

The net effect is that the user gets some visual feedback when the mouse moves over a control - just like a real toolbar. The toolbar simulation only goes so far, however. It's not possible to resize the UserForm (for example, make the images display vertically rather than horizontally). And, of course, it's not possible to dock the pseudo-toolbar to one of the Excel window borders.

Tip  

The images displayed on the controls are characters from the Wingding font. I used Excel's Insert image from book Text image from book Symbol command to enter the character into a cell . Then I copied it to the Clipboard and pasted it into the Picture property in the Properties box. This is a quick and easy way to add images to UserForm controls.




Excel 2007 Power Programming with VBA
Excel 2007 Power Programming with VBA (Mr. Spreadsheets Bookshelf)
ISBN: 0470044012
EAN: 2147483647
Year: 2007
Pages: 319

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