ListAllFacescreates a temporary toolbar, cbr, using the Addmethod of the CommandBarscollection.
The toolbar is declared:
Temporary, which means that it will be deleted when you exit Excel, if it has not already been
Floating, rather than docked at an edge of the screen or a popup
Not to be a menu bar, which means that cbBar will be a toolbar
Atemporary control is added to cbr, using the Addmethod of the Controlscollection for the com-
mand bar, and assigned to ctl.
The Do...Loopcontinues looping until there are no more valid FaceId values. The Do...Loopincrements
iRow, which represents the row numbers in the worksheet. On every row, iColumnis incremented from
1to 10. jrepresents the columns of the worksheet. The value of iRowis increased by 1 for every itera-
tion of the code in the For...Next loop. iFaceId represents the FaceId. The FaceId property of ctl is
assigned the value of iFaceId, and the resulting image is copied to the worksheet.
Some button images are blank and some are missing. The blank images are copied without error, but the
missing images cause an error number 1004. When an error occurs, the code branches to the error trap at
Recover:. If the error number is 1004, the code resumes executing at the statement after the one that
caused the error, leaving an empty slot for the button image. Eventually the code gets to the last FaceId
in Office. This causes error number –2147467259. At this point the code clears the status bar, removes the
temporary command bar, and exits.
Creat ing New M enus
In versions of Office prior to Office 2007, you can add a new menu to the built-in command bars. The
code is still valid in Office 2007, but produces a different result. The new menu is displayed in the Add-
Ins tab of the Ribbon, as shown in Figure 15-7.
The code to create this menu is as follows:
Public Sub AddCustomMenu()
Dim cbr As CommandBar
Dim ctlMenu As CommandBarControl
‘Add new menu control
Set cbr = Application.CommandBars(“Worksheet Menu Bar”)
Set ctlMenu = cbr.Controls.Add(Type:=msoControlPopup)
‘Add controls to new menu control
The information you have gathered with the last three exercises is not documented
in any easily obtainable form by Microsoft. It is a valuable guide to the built-in but-
ton faces at your disposal. There is an add-in application, CBList.xla, available
with the code that accompanies this book that makes it easy to generate these lists.
Chapter 15: Command Bars