Creating a Toolbar

In previous versions of Office, you could manually create a simple toolbar with buttons and dropdowns. Now you can only do this using VBA code. The more complex controls, such as those of type msoControlEdit, msoControlDropdown, and msoControlComboBox, have always required VBA code. As with the new menu created earlier, Excel 2007 displays the new toolbar in the Add-Ins tab of the Ribbon. The toolbar in Figure 15-8 contains three controls.

The first is of type msoControlButton and displays the user form for the data list.

Ï

CcmmandBars^.tarti - Micrastttt Eycel

- H

P ^ Heme insert

Page Lavcut Formulai Data Review V^sAi

DeVâBpér Aifd-Ins | 'S1

y

Custom ""

Sort- i[AII)

Menu Commsntls

Al

h

Name

A

S 1 c

u

E ! F 1 G

H

1

j

K L

M

1 [Name lAqe Sex

Married

Departmenl

2 IBM

36 M

■JALSE

SL

_

3 iFrancine

23 F

TRUE

MK

4 I Harry

46 M

TRUE

HR

5 ¡Jackie S ¡Joan

32 F 37 F

TRUE TRUE

MF MK

7 |john

44 M

TRUE

DS

8 1 Kendal

43 M

FALSE

HR

Cf iKfeili/ dfl F

Fil ÎF

"IL

ÜEady S

M

¿i ' - ---hU

Figure 15-8

The second control is of type msoControlPopup and displays two controls of type msoControlButton, as shown in Figure 15-9.

Dr d

ï

CcmmandBarsailsm - Microsoft Ëflcel

- =

£

±> m

Hcirit

îrisErt PagElaycut Formulât Data htVicVi Viöw

CWalcper Atfd-Ins |

#

- ~ *

Cl]£tOFÏ>

- £

(Ail)

Sort Ascend

ng

A1 Sort Decendrng

torrt 1 oclbar.-

HI IL LommancL

Al

-(

t-

Name

A

fi-

c

D

E F E H

1

J

K

L

M

1

Name

lAge

Sex Married

Department

2

Bill

36 M

FALSE

SL

3

Francine

23 .F

TRUE

MK

4

Harry

46 M

TRUE

HR

5 S

Jackie Joan

32 F 37 F

TRUE TRUE

MF MK

7

John

44 M

TRUE

DS

6 ¡Kendal

43 M

FALSE

HR

1 H

iüeJii/-

Al1 F.

-—

Btady S

m

a |D

atloos

¡Pli

The third control is of type msoControlDropdown and applies an AutoFilter on Department, as shown in Figure 15-10.

iEfci d

CammantÎBars^Jsm - Mi'lliMilf

s

|f*i Hcrné InsErt PagE tsymrt

F/rrmtilas Date Re^'r^i ViBW DE^EicpfT | Arid-lri!

$ -i "=■■ *

Custom- &ort-

MF

M

AD

IV'^T Commande lue

CR D5

Al

HR

»

ï

A B G

MF

ï ' F E

H

¡L

«Ji K

L M

! iNaitie * lAqe ' Sé

MK

rtme

5

Jackie 32'F

RD

15

Simon 43 M

St

16

Terry 41 ■ M

4

19

20

21

22

■n

--:-

Reacfy 3 of 17 records f ou r>d

g

The following code creates the toolbar:

Public Sub CreateToolbar()

'Get rid of any existing toolbar called Manage Data On Error Resume Next CommandBars("Manage Data").Delete On Error GoTo 0

'Create new toolbar

With CommandBars.Add(Name:="Manage Data")

With .Controls.Add(Type:=msoControlButton) .OnAction = "ShowDataForm" .Faceld = 264

.TooltipText = "Show Data Form"

End With

With .Controls.Add(Type:=msoControlPopup) .Caption = "Sort"

.TooltipText = "Sort Ascending or Descending"

With .Controls.Add(Type:=msoControlButton) .Caption = "Sort Ascending" .Faceld = 210 .OnAction = "SortList" .Parameter = "Asc" End With

With .Controls.Add(Type:=msoControlButton) .Caption = "Sort Descending" .Faceld = 211 .OnAction = "SortList" .Parameter = "Dsc"

End With End With

With .Controls.Add(Type:=msoControlDropdown) .Addltem "(All)" .AddItem "AD" .AddItem "CR" .AddItem "DS" .AddItem "HR" .AddItem "MF" .AddItem "MK" .AddItem "RD" .AddItem "SL"

.OnAction = "FilterDepartment" .TooltipText = "Select Department" End With

.Visible = True

End With

End Sub

The toolbar itself is very simple to create. CreateToolbar uses the Add method of the CommandBars collection and accepts all the default parameter values, apart from the Name property. The first control button is created in much the same way as a menu item, using the Add method of the Controls collection. It is assigned an OnAction macro, a Faceld, and a ToolTip.

The second control is created as type msoControlPopup. It is given the Caption of Sort and a ToolTip. It is then assigned two controls of its own, of type msoControlButton. They are assigned the SortList macro and Parameter values, as well as Facelds and captions.

Finally, the control of type msoControlDropdown is added. Its drop-down list is populated with department codes and its OnAction macro is FilterDepartment. It is also given a ToolTip. The last action is to set the toolbar's Visible property to True to display it.

The FilterDepartment macro follows:

Sub FilterDepartment()

Dim sDept

As String

With CommandBars.ActionControl

sDept

= .List(.ListIndex)

End With

If sDept =

= "(All)" Then

Range(

"Database").Parent.AutoFilterMode = False

Else

Range(

"Database").AutoFilter Field:=5, Criteria1:=sDept

End If

End Sub

A drop-down control has a List property that is an array of its list values and a Listlndex property that is the index number of the current list value. The ActionControl property of the CommandBar object, which refers to the currently active control, is a quick way to reference the control and access the List and Listlndex properties to get the department code required. The code is then used to perform the appropriate AutoFilter operation. If the (All) option is chosen, the AutoFilterMode property of the worksheet that is the parent of the Database Range object is set to False, removing the AutoFilter drop-downs and showing any hidden rows.

It is a good idea to run CreateToolbar from the Workbook_Open event procedure, and to delete the toolbar in the Workbook_BeforeClose event procedure. The toolbar will remain permanently in Excel if it is not deleted, and will give unexpected results if its buttons are pressed when other workbooks are active. If you do refer to command bars directly in workbook event procedures, you need to qualify the reference with Application:

Application.CommandBars("Manage Data").Delete

0 0

Post a comment