Professional Excel Development [Electronic resources] : The Definitive Guide to Developing Applications Using Microsoft® Excel and VBA® نسخه متنی

Loading Custom Icons from Files

As explained in the section on the FaceID setting above, each version of Excel from 2000 onward uses a slightly different UI drawing technique. This means custom icons stored as pictures on a worksheet will only appear correctly in the version of Excel in which they were optimized.

For applications running under Excel 2002 or higher, there is a second way to apply custom icons to command bar controls that eliminates the problems associated with the different drawing techniques used by different versions of Excel. The command bar builder supports this method automatically, but in this section we will explain how to use it manually so you can take advantage of it in an application that does not use the command bar builder.

Beginning with Office XP, two new properties were added to the CommandBarButton object. These two properties enable you to load icons directly into the control. The Picture property specifies the bitmap to be used as the foreground of the icon and the Mask property specifies the bitmap that indicates which areas of the icon should be rendered as transparent background. In the next section, we show you how to create these bitmaps.Chapter 20 Combining Excel and Visual Basic 6 we show how you can package up all of your custom icons into a single DLL resource file. Alternatively, if you are using the command bar builder, you can host both the icon and mask picture files on the wksCommandBars worksheet.

Creating Bitmap Files for Icons and Masks

If you plan to create custom icons on a regular basis, it probably makes sense to purchase a special-purpose icon creation program. For the occasional custom icon, however, every version of Windows comes with a perfectly serviceable icon creation tool: Microsoft Paint.

The type of file you will need to create for use as a CommandBarButton icon is a 16x16 pixel 16-color bitmap. The icon file will contain the artistic foreground picture you think of as your icon. The mask file is an overlay for the icon file in which everything foreground is colored black and everything you want to be transparent background is colored white.

To create a custom icon, open Microsoft Paint. Paint will open with a blank default image canvas. Select Image > Attributes from the Paint menu. In the Attributes dialog, set Width and Height to 16, Units to Pixels and Colors to Color. These settings are shown in Figure 8-14.

Figure 8-14. The Icon Attributes Settings

Click the OK button on the Attributes dialog and select View > Zoom > Custom from the Paint menu. Select 800% as your Zoom to setting. The resulting image canvas is shown in Figure 8-15.

Figure 8-15. The Blank Icon Canvas in Paint

You are now ready to draw your icon. Drawing icons well requires much practice. The appearance of a 16x16 pixel image at 800 percent magnification is often nothing like its appearance at normal size. We've provided a simple custom icon image you can use for testing purposes. This icon is called Arrows.bmp and is located on the CD in the \Concepts\Ch08Advanced Command Bar Handling folder. This icon is shown loaded into Paint in Figure 8-16.

Figure 8-16. The Arrows Icon

The mask file for the arrows icon is called ArrowsMask.bmp and is located in the same CD folder as the Arrows.bmp file. As you can see in Figure 8-17, this file simply replaces the blue foreground color of the original icon with black. When loaded into the CommandBarButton, the areas of the icon corresponding to the black areas of the mask will display, while the areas of the icon corresponding to the white areas of the mask will be transparent.

Figure 8-17. The Mask File for the Arrows Icon

Using Bitmap Files as CommandBarButton Icons

Now that we have our icon and mask bitmaps, it's a simple matter to apply them to a CommandBarButton. The procedures shown in Listing 8-7 build a command bar with a single button that uses our custom arrow icon and mask. You can find this code in the LoadPictureAndMask.xls workbook located on the CD in the \Concepts\Ch08Advanced Command Bar Handling folder. Note that this example only works in Excel 2002 or later.

Listing 8-7. Adding a Custom Icon to a CommandBarButton

Public Sub CreateBar()
Dim cbrBar As CommandBar
Dim ctlControl As CommandBarButton
Dim sPath As String
' Make sure any previously created version of our demo
' command bar is deleted.
RemoveBar
' We're assuming that the bitmap files used to create the
' custom icon are located in the same path as this workbook.
sPath = ThisWorkbook.Path
If Right$(sPath, 1) <> "\" Then sPath = sPath & "\"
' Create a toolbar-type command bar.
Set cbrBar = CommandBars.Add("Demo", msoBarTop, False, True)
cbrBar.Visible = True
' Add the command bar button control.
Set ctlControl = cbrBar.Controls.Add(msoControlButton)
' Load the foreground bitmap file.
ctlControl.Picture = LoadPicture(sPath & "Arrows.bmp")
' Load the mask bitmap file.
ctlControl.Mask = LoadPicture(sPath & "ArrowsMask.bmp")
End Sub
Public Sub RemoveBar()
On Error Resume Next
CommandBars("Demo").Delete
End Sub

To create the command bar button with the custom icon, run the CreateBar procedure. To remove the demo command bar and its button, run the RemoveBar procedure. The resulting custom icon appears as shown in Figure 8-18.

Figure 8-18. The Arrows Custom Icon