Using Other Types Of Command Bar Controls

A standard toolbar button is just one type of five control types that you can add to a toolbar. The control type is determined by the Type property of the control.

The built-in constants for the control types that you can add to a command bar are as follows:

♦ msoControlButton: A standard button.

♦ msoControlEdit: An edit box.

♦ msoControlComboBox: A combo box.

♦ msoControlDropdown: A drop-down list.

♦ msoControlButtonPopup: A button that, when clicked, displays other controls. Use this control to create a menu with menu items.

The Type property for a Control object is a read-only property that's set when the control is created. In other words,you can't change a control's type after it has been created.

The MakeMonthList procedure in Listing 22-5 creates a new toolbar, adds a drop-down list control, and fills that control with the names of each month. It also sets the OnAction property so that clicking the control executes a procedure named PasteMonth, which pastes the selected month into the active cell. The resulting toolbar is shown in Figure 22-15.

IIWBIW^

A

B

c

D

E

F .3

3

4

5

January

|

MonthLi ▼ x

7

Apri

B

8

February

9

10

Has

11

May

12

July August

13

14

September

October

November

15

_

T

16

December

_

► H |\ Sheet 1 /

M 1 Hill

Figure 22-15: This toolbar contains a drop-down list control, with an attached macro.

Figure 22-15: This toolbar contains a drop-down list control, with an attached macro.

Listing 22-5: Attaching a Drop-down List to a Command Bar

Sub MakeMonthList()

Dim TBar As CommandBar

Dim NewDD As CommandBarControl

' Delete existing toolbar if it exists On Error Resume Next CommandBars("MonthList").Delete On Error GoTo 0

Listing 22-5 (Continued)

' Create a new toolbar

Set TBar = CommandBars.Add With TBar

.Name = "MonthList" .Visible = True End With

' Add a DropDown control

Set NewDD = CommandBars("MonthList").Controls.Add _

(Type:=msoControlDropdown) With NewDD

.Caption = "DateDD" .OnAction = "PasteMonth" .Style = msoButtonAutomatic

Fill it with month name For i = 1 To 12

.AddI tem Format(DateSerial(1, i, 1), "mmmm") Next i

.ListIndex = 1 End With End Sub

The PasteMonth procedure follows:

Sub PasteMonth()

' Puts the selected month in the active cell On Error Resume Next

With CommandBars("MonthList").Controls("DateDD")

ActiveCell.Value = .List(.ListIndex) End With End Sub

The workbook has an additional twist: It uses a Worksheet_SelectionChange event handler. This procedure, which follows, is executed whenever the user makes a new selection on the worksheet. The procedure determines whether the active cell contains a month name. If so, it sets the ListIndex property of the drop-down list control in the toolbar.

Private Sub Worksheet_SelectionChange(ByVal Target _ As Excel.Range) For i = 1 To 12

Set ActCell = Target.Range("A1") If ActCell.Value = Format(DateSerial(1, i, 1), "mmmm") Then

CommandBars("MonthList").Controls("DateDD")

.Listlndex = i Exit Sub End If Next i End Sub

Chapter 23

0 0

Post a comment