Cells(iRow, 4).Value = ctl.ID
iRow = iRow + 1
Application.StatusBar = False
ListFirstLevelControlsfirst checks that the active sheet is an empty worksheet, using the
IsEmptyWorksheetfunction that is shown in the following code. It then uses OnErrorResume
Nextto avoid run-time errors when it tries to access control images that do not exist. In the outer
ForEach...Nextloop, it assigns a reference to each command bar to cbr, shows the Nameproperty
ofthe command bar on the status bar so you can track what it is doing, and places the Namein the
Acolumn of the current row, defined by iRow.
The inner ForEach...Nextloop processes all the controls on cbr, placing the Captionproperty of
each control in column B. It then attempts to use the CopyFacemethod of the control to copy the con-
trol’s image to the clipboard. If this does not create an error, it pastes the image to column C and places
the value of the FaceIdproperty in the same cell. It places the IDproperty of the control in column D.
Itclears any errors, increments iRowby one, and processes the next control.
The IsEmptyWorksheetfunction, shown next, checks that the input parameter object shtis a
worksheet. If so, it checks that the count of entries in the used range is 0. If both checks succeed, it
returns True. Otherwise, it issues a warning message and the default return value, which is False, is
Function IsEmptyWorksheet(sht As Object) As Boolean
‘If sht is a worksheet, count the non empty cells
If TypeName(sht) = “Worksheet” Then
If WorksheetFunction.CountA(sht.UsedRange) = 0 Then
IsEmptyWorksheet = True
This example, and the two following, can take a long time to complete. You can
watch the progress of the code on the status bar. If you only want to see part of the
output, press Ctrl+Break after a minute or so to interrupt the macro, click Debug,
and then choose Run➪Reset.
Chapter 15: Command Bars