Controls at All Levels

Figure 15-5 and the following code take the previous procedure to greater levels of detail. All controls are examined to see what controls are contained within them. Where possible, the contained controls are listed. Some controls, such as those containing graphics, can't be listed in greater detail. The information on sub-controls is indented across the worksheet. The code is capable of reporting to as many levels as there are, but Excel 2007 does not have controls beyond the fourth level.

Oi] _ i-n

- - V

A B

C D

E r

Gl

H 1 ■

I

Worksheet Menu Bar l&File

10 SiNevu...

10

18

2

SOpen

iS

23

3

&Ckise

<m

106

4

&Save

1 y

3

5

Sawe SAs

748

6

Save ss Weö Pa&ge

iH

3823

7

Save StWorkspace..

1Q

346

B

'File Seatc&h

i'a

5905

9

Per&missiori:..

iO

7994

10

Per&missian

10 StUnrestricted Access

1

7990

11

SRestncted Access

1

7991

12

^Manage Credentials

1

¡(0014

13

CtiSteck Out

IfiJ

6127

14

QiSeck In

10

6128

15

Ve&rsion History...

7799

16

WaStr Page Preview

li

3655

11

Page SetSup

tea

247

18

Pfin&t Area

10 &SbI Print Ares

1D

364

19

SCIear Print Area

1

1584

20

Punt Pre&view

ia

109

21

ftPrJnt

iS

4-

22

Sen&dTo

10 &Mail Recipient

10

3733

23

Original &Sender

10

B139

24

Mail Re&cipient (for Review}..

5958

25

M&ail Recipient fas Attachment).

m

2188

~2G

^Exchange Folder.

iS

933

27"

^Online Meeting Participant

1

3728

23

Recipient using Internet Fa&x Service.

7392

29 n

> M FT! lÄ/st i AH Levels

Propeitfiiss

IIP

750

Here is the code to list controls at all levels:

Sub ListAllControls()

Dim cbr As CommandBar Dim rng As Range

Dim ctl As CommandBarControl

'Test for empty worksheet and freeze screen If Not IsEmptyWorksheet(ActiveSheet) Then Exit Application.ScreenUpdating = False

Sub

'Start in A1 cell Set rng = Range("A1")

'Loop through all commandbars

For Each cbr In Application.CommandBars

Application.StatusBar = "Processing Bar "

& cbr.Name

'List name of bar rng.Value = cbr.Name

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

'Call ListControls function

Set rng = rng.Offset(ListControls(ctl,

rng))

Next ctl

Next cbr

'Fit columns to data Range("A:J").EntireColumn.AutoFit

Application.StatusBar = False

End Sub

ListAllControls loops through the CommandBars collection, using rng to keep track of the current A column cell of the worksheet it is writing to. It posts the name of the current command bar in a message on the status bar, so you can tell where it is up to, and also enters the name of the command bar at the current rng location in the worksheet. It then loops through all the controls on the current command bar, executing the ListControls function, which is shown in the next code snippet.

In Chapter 26, you will need to get listings of the VBE command bars. You can easily accomplish this by changing the following line:

For Each cBar in Application.CommandBars

For Each cBar in Application.VBE.CommandBars

ListControls is responsible for listing the details of each control it is passed and the details of any controls under that control, starting at the current rng location in the worksheet. When it has performed its tasks, ListControls returns a value equal to the number of lines that it has used for its list. Offset is used to compute the new rng cell location for the start of the next command bar's listing:

Function ListControls(ctl As CommandBarControl, rng As Range) As Long Dim lOffset As Long 'Tracks current row relative to rng Dim ctlSub As CommandBarControl 'Control contained in ctl

'Ignore Errors On Error Resume Next

'Start in rng cell lOffset = 0

'List control name and type rng.Offset(lOffset, 1).Value = ctl.Caption rng.Offset(lOffset, 2).Value = ctl.Type

'Attempt to copy control face. If error, don't paste ctl.CopyFace

If Err.Number = 0 Then

ActiveSheet.Paste rng.Offset(lOffset, 3) rng.Offset(lOffset, 3).Value = ctl.Faceld End If Err.Clear

'Check Control Type Select Case ctl.Type

'Do nothing for these control types

Case Else

'Call function recursively if current control contains other controls For Each ctlSub In ctl.Controls lOffset = lOffset + _

ListControls(ctlSub, rng.Offset(lOffset, 2))

Next ctlSub lOffset = lOffset - 1 End Select

ListControls = lOffset + 1 End Function

ListControls is a recursive function, and it runs itself to process as many levels of controls as it finds. It uses lOffset to keep track of the rows it writes to, relative to the starting cell rng. It uses very similar code to ListFirstLevelControls, but records the control type as well as the caption, icon, and face ID. Most of the control types are:

□ 1—msoControlButton

However, you will see other types in the list as well:

2-

-msoControlEdit

4-

-msoControlComboBox

6-

-msoControlSplitDropdown

7-

-msoControlOCXDropdown

13

—msoControlSplitButtonPopup

18

—msoControlGrid

The Select Case construct is used to avoid trying to list the sub-controls where this is not possible.

When ListControls finds a control with sub-controls it can list, it calls itself with a rng starting point that is offset from its current rng by lOffset lines down and two columns across. ListControls keeps calling itself as often as necessary to climb down into every level of sub-control, and then it climbs back to continue with the higher levels. Each time it is called, it returns the number of lines it has written to, relative to rng.

0 0

Post a comment