Excels Builtin Command Bars

Before launching into creating your own command bars, it will help to understand how the built-in command bars are structured. You can use the following code to list the existing command bars and any that you have added yourself. It lists the name of each command bar in column A and the names of the controls in the command bars Controls collection in column B, as shown in Figure 15-4. The code does not attempt to display lower-level controls that belong to controls such as the File menu on the Worksheet menu bar, so the procedure has been named ListFirstLevelControls.

m

I ;■ mil i:nd Earn rstilsp

— □ V

A

ja

C

-D ■ I

1

CommandBar

ft'nriti ot

FacelD

ID

2

Worksheet Menu Bar

3

SFIIe

3000Z

4

SEdlt

30003

5

SView

30004

6

Slnsert

30006

7"

FSormat

30006

S

STools

30007

9

SData

30011

10

A&ctlon

300S3

15

SWIndow

30009

12

SHelp

30010

13~

Chart Menu Bar

14

SFIIe

30002

15

SEdit

30003

16

¿View

30004

u

Slnsert

30005

18

FSormat

30006

STools

30007

no

SCharf

30022

21

A&ctlon

30083

22

SWIndow

30009

23

SHelp

30010

24

WordArt

25

SWordArt

■ 1031

1031

36

Edit Te&xt

B 2094

2094

21

SWordArt Gallery

□j 1606

1606

2E

SObject

962

362

29

SWordArt Shape

1058

3D

SWordArt Same Letter Heights

Aa 1063

1063

31

SWordArt Vertical Text

|| 1061

1081

32

SWordArt Alignment

1059

33'

R.Wnrr1 Art Tharartiir Rrtqrinn • H First Level All Li jels PacelD: Popuos J'JiLI

■fan

The macro also shows the control's Id property value, in all cases, and its image and its Faceld property value when such an image exists. Although you can no longer display the built-in command bars, you can use the controls and images they contain in your own command bars.

Make sure you are in an empty worksheet when you run this macro and the following two examples. They contain tests to make sure they will not overwrite any data in the active sheet.

If you are testing this code, it should be placed in a standard code module, not in a class module. Don't put the code in the ThisWorkbook module or a class module behind a worksheet. You should also include the IsEmptyWorksheet function listed further down.

Here is the code to list the first-level controls:

Sub ListFirstLevelControls()

Dim ctl As CommandBarControl Dim cbr As CommandBar Dim iRow As Integer

If Not IsEmptyWorksheet(ActiveSheet) Then Exit Sub

'Ignore errors and freeze screen On Error Resume Next Application.ScreenUpdating = False

'Enter headings

Cells(1, 1).Value = "CommandBar" Cells(1, 2).Value = "Control" Cells(1, 3).Value = "FaceID" Cells(1, 4).Value = "ID"

'Loop through all commandbars For Each cbr In CommandBars

Application.StatusBar = "Processing Bar " & cbr.Name Cells(iRow, 1).Value = cbr.Name iRow = iRow + 1

'Loop through controls on commandbar For Each ctl In cbr.Controls

Cells(iRow, 2).Value = ctl.Caption

'Try to get image ctl.CopyFace

If Err.Number = 0 Then

ActiveSheet.Paste Cells(iRow, 3) Cells(iRow, 3).Value = ctl.FaceId

End If

Cells(iRow, 4).Value = ctl.ID Err.Clear iRow = iRow + 1

Next ctl

Next cbr

Range("A:D").EntireColumn.AutoFit Application.StatusBar = False End Sub

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 O Reset.

ListFirstLevelControls first checks that the active sheet is an empty worksheet, using the IsEmptyWorksheet function that is shown in the following code. It then uses On Error Resume Next to avoid run-time errors when it tries to access control images that do not exist. In the outer For Each...Next loop, it assigns a reference to each command bar to cbr, shows the Name property of the command bar on the status bar so you can track what it is doing, and places the Name in the A column of the current row, defined by iRow.

The inner For Each...Next loop processes all the controls on cbr, placing the Caption property of each control in column B. It then attempts to use the CopyFace method of the control to copy the control'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 FaceId property in the same cell. It places the ID property of the control in column D. It clears any errors, increments iRow by one, and processes the next control.

The IsEmptyWorksheet function, shown next, checks that the input parameter object sht is 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 returned:

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

Exit Function End If End If

MsgBox "Please make sure that an empty worksheet is active" End Function

Was this article helpful?

0 0

Post a comment