Listing Adding Clickable Sorting to Worksheet Lists

Option Explicit

Dim mnDirection As Integer Dim mnColumn As Integer

Private Sub Worksheet_BeforeDoub1eC1ick(ByVa1 Target As Range, Cancel As Boolean) ' make sure the double-click occurred in a cell ' containing column labels If Target.Column < 5 And Target.Row = 1 Then ' see if we need to toggle the ' direction of the sort If Target.Column <> mnColumn Then ' clicked in new column - record

' which column was clicked mnColumn = Target.Column ' set default direction mnDirection = xIAscending

Else

' clicked in same column ' toggle the sort direction If mnDirection = xIAscending Then mnDirection = xlDescending

Else mnDirection = xIAscending End If End If TestSort End If

End Sub

Private Sub TestSort() Dim rg As Range

' get current region associated with cell A1 Set rg = Me.Ce11s(1, 1).CurrentRegion

' ok - sort the list rg.Sort Key1:=rg.Ce11s(1, mnColumn), _ Order1:=mnDirection, _ Header:=x1Yes

Set rg = Nothing

End Sub

I think this is a good example because it incorporates some of the things you learned earlier in the book but haven't used very much yet, such as event handling, an appropriate use of module-level variables, and the use of named parameter passing (as opposed to passing parameters by order). Oh, and I demonstrate the Sort method—the main purpose of this example.

The first thing to note is the two module-level integer variables: mnDirection and mnColumn. You'll use these two variables to store which column was last sorted and the direction in which it was sorted.

This functionality wouldn't be possible without the ability to capture the double-click event. The code within this event handler executes any time you double-click the worksheet. By inspecting the Column and Row properties of the Target parameter, you can figure out if the double-click occurred on the header row of your list or not. The Target parameter is a Range object that represents the cell that was double-clicked.

If the double-click occurred on one of the header row cells, the next task is to figure out whether it occurred in the same column as the last sort or not. If the double-click occurred in a different column, we need to update the mnColumn variable with the new column number and set the mnDirection variable to a default value (ascending). If the double-click occurred in the same column, you simply need to toggle the mnDirection variable. Note that you use the Excel-defined constants xlAscending and xlDe-scending to set the mnDirection variable. This helps make your code easier to read and understand.

The last thing to do is call the TestSort procedure that actually performs the sort. TestSort uses the Cur-rentRegion property of cell A1 to determine the boundaries of your list. For sorting, all you need to do is specify the key or column that should be sorted and the order of the sort, and then you should indicate whether or not there is a header row. Because you don't need to use many of the Sort method's parameters, it's better here to use the named parameter method of passing parameters rather than using a comma-separated ordered list as you have for most of the other examples in the book. You can see the intended result in Figures 9.13 and 9.14: a list that can be sorted just by clicking on the column headers.

Figure 9.13

Providing the custom sorting functionality in Excel is almost trivial it is so easy. I have double-clicked the Sales label in this picture.

[£] Microsoft. Office Excef 20D3 Beta - Chapler 9 Eh

as®

M

Bis Edt

ttaw

InsErt

Format look Qî

' vVrdc.v Hntl

_ a

X

m

Miz^

e

£ =

.op H^n =r-

;

l_j

■.i

u y

K gLälMl

rpIRflekMkÜBflflfiii

.. [JhÜF-tV^i,

g

CM

S SALES

A

B_

b

d 1

E

A

: 1

TRANS 10 PRODUCT

REGIQN

1 SALES 1

?

2

DLX

NORTHEAST

76

3

15

STD

SOUTHERN

333

t

IB

EID

SOUTHWEST

433 '

5

1

DLX

MIDWEST

443

6

13

NORTHEAST

45s '

7

14

SID

ROCKY riTTN

569 '

8

5

DLX

SOUTHWEST

654

9

7

DLX

WESTERN

657

10

3

DLX

NORTH EAST

?74 '

11

12

STD

NORTHEAST

393

1?

9

SID

MIDWEST

999

P

5

WESTERN

1.433

14

B

DLX

UNKNOWN

2,345

15

10

STD

NORTHEAST

2345

16

11

STD

NORTHEAST

17

4

DLX

SOUTHWEST

IS

19

30

:i

>i

V

IN 1

* »]/ Qtrwit!■.'::Lion / Ca« /ihoEti

lij<

If I Double-clicking Sales again, the sort order is reversed.

Figure 9.14

If I Double-clicking Sales again, the sort order is reversed.

üU MErtWOft II. ,1k .- ... t .11.. 1 ' ...1.- '):-.

BBH

1 ^ Bis

Edt

Vs..',1 [nswt

Fjjrrrtst fools Data Vlndc-v Hot

_ «

X

-L

10 -

[il/ =

*

A .

i

i J

"ZHj

* Vi - ¿J

'.'RtfJy^i

h Ci.=.ny^..

S

D1

S SALES

A

B

C

Ti |—

E

-

-"v

: 1

TRAMS ID PRODUCT

REGIQN

1 SALES 1

?

10

SID

NORTHEAST

2,945

3

B

DLX

UNKNOWN

2,345

.4

E

WESTERN

1,433 '

5

9

SID

MIDWEST

993

6

12

STD

NORTHEAST

393

7

3

DLX

NORTHEAST

774 '

6

7

DLX

WESTERN

657

9

5

DLX

SOUTHWEST

354

ID

14

SID

ROCKY MTN

569

il

13

NORTHEAST

453

1?

1

DLX

MIDWEST

449 '

i3"

IB

STD

SOUTHWEST

433

Ï4

15

SID

SOUTHERN

332

15

2

DLX

NORTHEAST

76

16

11

STD

NORTHEAST

17

4

DLX

SOUTHWEST

IS

19

30

:i

>i

V

IN 1

\i Qtranti.L.qon / ^ecHlCdte /ihoeti

1 Ready

0 0

Post a comment