Adding Combo Boxes

You can make filtering even easier for a user by placing controls in the worksheet to run AutoFilter. This also gives you the opportunity to do far more with the data than filter it. You could copy the filtered data to another worksheet and generate a report, you could chart the data, or you could delete it. Figure 6-7 shows two ActiveX combo box controls that allow the user to select the month and year required.

DataLrsti. :15m

A 1 £

I c

a

E

F

G -H

-

1

J. I May

dl 2007

d

Copy To New Sheet |

3

4

293 May 04. 2007 Pradesh

VIC

Oranges 493 15 7395

294 May 95 2B07 Kee

WA

Mangoes

756

20

15320

295 May 05. 2007 Roberta

VIC

Mangoes

580

20

11600

296 May 0 6 2007 Pradesh

WA

Apples

990

125

12375

297 May 07. 2007 Smith

3A

□ranges

536

15

8040

298 May 11 2007 Smith

TAS

Pears

690

IS

12420

299 May 11. 2007 Smith

WA

Oranges

790

IS

11850

30'J May 12. 2007 Smith

WA

Mangoes

804

20

1S080

301 May 13.2007 Robarts

SA

Apples

219

12.5

27376

1

302 May 14 2007 Roberts

VIC

Mangoes

449

20'

8980

303 May 18.2D07 Kee

TAS

Pears

365

MS

15570

304 May 13, 2007 Kee

QLQ

Oranges

961

1S

14415

305 May 18 2007 Kee

TAS

Pears

821

15

14778

306 May 20, 2007 Pradestl

NSW

Oranges

847

15

1Z705

307 May 21 2007 Smith

VIC

Mangoes

78

20

1560

300 May 25, 2007 Pradesh

VIC

Mangoes

845

tu

15900

309 May 26. 2007 Roberts

NSW

Mangoes

S9S

20

17960

310 May 27 20D7 Pradesh

WA

Apples

277

12 5

3452 5

311 May29,2007 Kee

WA

Mangoes

326

20

5520,

434I

435

praej ._.__,__

« • i- m Sales J

-

The combo boxes have the default names of ComboBoxl and ComboBox2. To place list values into the combo boxes, you can enter the list values into a worksheet column and define the ListFillRange property of the ComboBox object as something like "=Sheet2!A1:A12". Alternatively, you can use the following Workbook_Open event procedure in the ThisWorkbook module of the workbook to populate the combo boxes when the workbook is opened:

Private Sub Workbook_Open() Dim vMonths As Variant Dim vYears As Variant Dim i As Integer

'Create date arrays vMonths = Array("Jan", "Feb", "Mar", "Apr", "May", "Jun", _

"Jul", "Aug", "Sep", "Oct", "Nov", "Dec") vYears = Array(2006, 2007)

'Populate months using Addltem method For i = LBound(vMonths) To UBound(vMonths)

Sheetl.ComboBoxl.Addltem vMonths(i) Next i

'Populate years using List property

Sheet1.ComboBox2.List = WorksheetFunction.Transpose(vYears) End Sub

The Addltem method of the ComboBox object adds the Months array values to the ComboBoxl list. To show an alternative technique, the worksheet Transpose function is used to convert the Years array from a row to a column, and the values are assigned to the List property of ComboBox2.

Note that the programmatic name of Sheetl, which you can see in the Project Explorer window or the Properties window of the VBE, has been used to define the location of the combo boxes. Even though the name of the worksheet is Sales, the programmatic name is still Sheetl, unless you change it at the top of the Properties window where it is identified by (Name), rather than Name, as shown in Figure 6-8.

p Properties -

Sheetl >-IAl£3'

Sheetl Worksheet

M

Alphabetic j Categorized

{Name}

ïheetl

DisplayPageBreaks

False

DisplsyRightToLefl:

False

EnableAutoRlter

False

EnableCalcutation

Ttue

EnableFormatConditionst True

EnableOutlining

False

EnablePivotTable

False

EnableSelection

0 - wNoRestrictions

Name

Sales

SarollArea

StandardWidth

a.-u

Visible

-1 -ulSheetVsible

Figure 6-8

In the code module behind the worksheet, the following code is entered:

Private Sub ComboBox1_Click()

If ComboBox2.Value="" Then

Exit

Sub

Call FilterDates

End Sub

Private Sub ComboBox2_Click()

If ComboBox1.Value="" Then

Exit

Sub

Call FilterDates

End Sub

As long as the other combo box has been assigned a value, when you click an entry in their drop-down lists, each combo box executes the FilterDates procedure, which is described next. FilterDates can be in the same module and declared Private if you do not want any other modules to be able to use it, or it can be in a standard code module if you want to use it as a general utility procedure.

So, how do you construct the FilterDates procedure? As shown in previous chapters, you can use the macro recorder to get something to start with, and then refine the code to make it more flexible and efficient. If you use the macro recorder to record the process of filtering the dates, you will get code like this:

ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=1, Criteria1:= _ ">=1/01/2007", Operator:=xlAnd, Criteria2:="<=31/01/2007"

You might notice that the dates have been translated to the format of the regional settings — in this case that of Australia, which uses the same format as the UK. The format generated by the recorder is d/mm/yyyy. Also note that the dates are formatted as text, rather than dates, because the criteria must include the logical operators.

Date Format Problems

Unfortunately, the previous code does not perform as expected. When you run the recorded macro, the dates are interpreted by VBA as U.S. dates in the format mm/dd/yyyy. Criteria2 is not understood by VBA. To make this macro perform properly, you need to convert the dates to a U.S. format. Of course, you will not have this problem with your recorded code if you work with U.S. date formats in your regional settings in the first place.

Trying to make your VBA code compatible with dates in all language versions of Excel is very difficult. See Chapter 25 for more details.

The following FilterDates procedure is executed from the Click event procedures of the combo boxes, and it computes the start and end dates required for the criteria of the AutoFilter method. FilterDates has been placed in the same module as the combo box event procedures and declared as Private, so it does not appear in the Macro dialog box:

Private Sub FilterDates()

Dim iStartMonth As Integer Dim iStartYear As Integer Dim dteStartDate As Date

Dim dteEndDate As Date

Dim sStartCriterion As String

Dim sEndCriterion As String

'Get Date values iStartMonth = Me.ComboBoxl.Listlndex + 1 iStartYear = Me.ComboBox2.Value

'Calculate date values and format as US Dates dteStartDate = DateSerial(iStartYear, iStartMonth, 1) dteEndDate = DateSerial(iStartYear, iStartMonth + 1, 1) sStartCriterion = ">=" & Format(dteStartDate, "mm/dd/yyyy") sEndCriterion = "<" & Format(dteEndDate, "mm/dd/yyyy")

'Apply AutoFilter

Me.ListObjects("Table1").Range.AutoFilter _ Field:=1, _

Criteria1:=sStartCriterion, _ Operator:=xlAnd, _ Criteria2:=sEndCriterion

End Sub

FilterDates assigns the values selected in the combo boxes to iStartMonth and iStartYear. The Me keyword has been used to refer to the sheet containing the code, rather than the object name Sheet1. This makes the code portable, which means it can be used in other sheet modules without worrying about the name of the sheet.

iStartMonth uses the Listlndex property of ComboBox1 to obtain the month as a number. Because the Listlndex is zero-based, 1 is added to give the correct month number. The DateSerial function translates the year and month numbers into a date and assigns the date to dteStartDate. The second DateSerial function calculates a date that is one month ahead of dteStartDate and assigns it to dteEndDate.

The Format function is used to turn dteStartDate and dteEndDate back into strings in the U.S. date format of mm/dd/yyyy. The appropriate logical operators are placed in front, and the resulting strings are assigned to sStartCriterion and sEndCriterion, respectively. FilterDates finally executes the AutoFilter method on the table Table1, using the computed criteria.

Getting the Exact Date

Another tricky problem with AutoFilter occurs with dates in all language versions of Excel. The problem arises when you want to get an exact date, rather than a date within a range of dates. In this case, AutoFilter matches your date with the formatted appearance of the dates in the worksheet, not the underlying date values.

Excel holds dates as numeric values equal to the number of days since Jan 1, 1900. For example, Jan 1, 2007 is held as 39,083. When you ask for dates greater than or equal to Jan 1, 2007, Excel looks for date serial numbers greater than or equal to 39,083. However, when you ask for dates equal to Jan 1, 2007, Excel does not look for the numeric value of the date. Excel checks for the string value "Jan 1, 2007" as it appears formatted in the worksheet and as it is returned by the Text property of the Range object.

The following adaptation of FilterDates will handle an exact date match in your list, because sExactCriterion is assigned the date value as a string, in the format "mmm dd, yyyy". It obtains the format from the worksheet using the NumberFormat property of the first cell in the body of the table, where the date is formatted as mmm dd, yyyy:

Sub

FilterExactDate()

Dim iExactMonth As Integer

Dim iExactYear As Integer

Dim dteExactDate As Date

Dim sExactCriterion As String

Dim sDateFormat As String

Dim loMyData as ListObject

'Get Date values

iExactMonth = Sheetl.ComboBoxl.Listlndex + 1

iExactYear = Sheet1.ComboBox2.Value

'Get Format from Table

Set loMyData = Sheet1.ListObjects("Table1")

sDateFormat = loMyData.DataBodyRange(1).NumberFormat

'Calculate as a date and format as in worksheet

dteExactDate = DateSerial(iExactYear, iExactMonth, 1)

sExactCriterion = Format(dteExactDate, sDateFormat)

'Filter Tablel

loMyData.Range.AutoFilter _

Field:=1, Criteria1:=sExactCriterion

End

Sub

The previous code will give all the entries for the first of the month, because 1 is specified as the third parameter in the DateSerial function. To select any day of the month, a third combo box could be added to cell A2 and some code added to the ComboBox1_Click event procedure to list the correct number of days for the month specified in ComboBoxl.

0 0

Post a comment