Forms Controls

Figure 10-3 shows a Form control that is being used to select a product name to be entered in column D. The control appears over any cell in column D that you double-click. When you select the product, the product name is entered in the cell "behind" the control, the price of the product is entered in column F on the same row, and the control disappears.

IsfltnJte- ■: I j m

— n-

v.

A ' B

' C

D

E 1

F ;

G

H S

1

Date Customer

State

Product

NumberSold

Price

Revenue

2

Jan 01 2007 Roberts

NSW

Bananas

903

515.00

S13.545.00

3'

Jan 01 2007 Roberts

TAS

Bananas

331

£15.00

$4 955 00

4

Jan 02. 2007 Smith

QLD

Mangoes

299

520.00

$5,980 00

5"

Jan 05. 2007 Roberts

QLD

Bananas

S12

115.00

$9 180 00

6

Jan 08 2007 Roberts

VIC

Lychees

907

512.50

511,337.50

7

Jan 08. 2007 Pradesh

TAS

Rambutan

107

£18.00

11 926 00

8

Jan 10 2007 Roberts

VIC

Lychees

770

£12.50

$9,625 00

S

Jan 14 2007 Smith

NT

Lychees

223

512.50

$2.787 50

10

Jan 14 2007 Smith

VIC

Bananas

132

515.00

$1,980 00

s

11

Jan 15. 2007 Pradesh

QLD

Bananas

£69

$15.00

£10 035 00

12

Jan 17, 2007 Roberts

NSW

Mangoes

B81

520.00

£17,620 00

13

Jan 21. 2007 Kee

SA

Rambutan

E24

£18.00

£11,232.00

14

Jan 22. 2007 Roberts

QLD

Ramfciutan

193

520.00

$3,860.00

15

Jan 23. 2007 Smith

SA

Mangoes

255

520.00

$5.100 00

16

Jan 27, 2007 Kee

QLD

Manqoes

6

520.00

5120.00

17

I-

18

Bananas

20

Mangoes Rambutan

21 ??

1

u

► » prnfir SatesData

i»i

Figure 10-3

If you hover your cursor over the Form button that creates the control shown in Figure 10-3, the ScreenTip that pops up describes this control as a ComboBox. However, in the Excel object model, it is called a DropDown object, and it belongs to the DropDowns collection.

The DropDown object is a hidden member of the Excel object model in Excel 97 and later versions. You will not find any help screens for this object, and it will not normally appear in the Object Browser. You can make it visible in the Object Browser if you right-click in the Object Browser window and select Show Hidden Members from the shortcut menu. You can learn a lot about the Forms toolbar controls by using the macro recorder and the Object Browser, but you will need to have access to Excel 5 or Excel 95 to get full documentation on them.

The DropDown control is created by a procedure called from the following BeforeDoubleClick event procedure in the SalesData sheet, which has the programmatic name Sheet2:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) If Not Intersect(Target, Columns("D")) Is Nothing Then Call AddDropDown(Target) Cancel = True End If End Sub

The event procedure checks that Target (the cell that was double-clicked) is in column D. If so, it then runs the AddDropDown procedure, passing Target as an input argument, and cancels the double-click event.

The following two procedures are in a standard module:

Sub AddDropDown(Target As Range) Dim ddBox As DropDown Dim vProducts As Variant Dim i As Integer

'Create array of products vProducts = Array("Bananas", "Lychees", "Mangoes"

, "Rambutan")

'Add the drop down control in Target cell With Target

Set ddBox = Sheet2.DropDowns.Add(.Left, .Top, End With

.Width, .Height)

'Define macro to run and populate list With ddBox

.OnAction = "EnterProdInfo"

For i = LBound(vProducts) To UBound(vProducts)

.AddItem vProducts(i) Next i End With

End Sub

Private Sub EnterProdInfo() Dim vPrices As Variant

'Create array of prices vPrices = Array(15, 12.5, 20, 18)

'Enter selected item into cell beneath drop down With Sheet2.DropDowns(Application.Caller) .TopLeftCell.Value = .List(.ListIndex)

.TopLeftCell.Offset(0, 2).Value = vPrices(.ListIndex + LBound(vPrices)

- 1)

'Delete drop down .Delete

End With End Sub

The AddDropDown procedure is not declared Private, because it would not then be possible to call it from the Sheet2 code module. This would normally be a problem if you wanted to prevent users from seeing the procedure in the Macro dialog box. However, because it has an input argument, it will not be shown in the dialog box anyway. Also, it does not matter whether AddDropDown is placed in the Sheet2 module or a standard module. It will operate in either location.

AddDropDown uses the Add method of the DropDowns collection to create a new drop-down. It aligns the new control exactly with Target, giving it the same Left, Top, Width, and Height properties as the cell. In the with...End With construction, the procedure defines the OnAction property of the dropdown to be the EnterProdlnfo procedure. This means that EnterProdlnfo will be run when an item is chosen from the drop-down. The For...Next loop uses the Addltemmethod of the drop-down to place the list of items in vProducts into the drop-down list.

EnterProdlnfo has been declared Private to prevent its appearance in the Macro dialog box. Although it is private, the drop-down can access it. EnterProdlnfo could have been placed in the Sheet2 code module, but the OnAction property of the drop-down would have to be assigned Sheet2.EnterProdInfo.

EnterProdlnfo loads vPrices with the prices corresponding to the products. It then uses Application.Caller to return the name of the drop-down control that called the OnAction procedure. It uses this name as an index into the DropDowns collection on Sheet2 to get a reference to the DropDown object itself. In the With...End With construction, EnterProdlnfo uses the Listlndex property of the DropDown object to get the index number of the item chosen in the drop-down list.

You cannot directly access the name of the chosen item in a DropDown object, unlike an ActiveX ComboBox object that returns the name in its Value property. The Value property of a drop-down is the same as the Listlndex, which returns the numeric position of the item in the list. To get the item name from a drop-down, you use the Listlndex property as a one-based index to the List property of the drop-down. The List property returns an array of all the items in the list.

The TopLeftCell property of the DropDown object returns a reference to the Range object under the top-left corner of the DropDown object. EnterProdlnfo assigns the item chosen in the list to the Value property of this Range object. It then assigns the price of the product to the Range object that is offset two columns to the right of the TopLeftCell Range object.

EnterProdlnfo also uses the Listlndex property of the drop-down as an index into the Prices array. The problem with this is that the drop-down list is always one-based, whereas the Array function list depends on the Option Base statement in the declarations section of the module. LBound(vPrices) - 1 is used to reduce the Listlndex value by 1 if Option Base 0 is in effect or by 0 if Option Base 1 is in effect.

You can also use the following code to ensure that the resulting array is zero-based under Option-Base-1: vPrices = VBA.Array(15, 12.5, 20, 18)

0 0

Post a comment