Hello All,

I'm trying to create a worksheet that has a database with the capability to
dynamically set each of the three sort keys. My database is in the range
from a5:g15, the field names are in row 5. I've created in cell drop down
lists using validation in cells a4, b4, c4. I'd like to use these dropdowns
to select sort keys 1-3 respectively.

Searching google I've found some posts that have something similar with a
single sort key that was posted by Steve Bell:

Dim x as Integer

x = Worksheetfunction.Match(Range("A3"),Range("A5:g15" ), 0)

Selection.Sort Key1:=Columns(x), Order1:=xlAscending, _
Header:=xlGuess,OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

but I can't figure out how to modify it to accomodate three sort keys. I've
tried dimensioning two other variables y and z for the other sort keys and
the pasting the above code with x changed to y and z respectively....no
luck, I keep getting errors.

This is what I have so far.

Dim x as Integer
Dim y as Integer
Dim z as Integer

x = Worksheetfunction.Match(Range("A3"),Range("A5:g15" ), 0)
y = Worksheetfunction.Match(Range("b3"),Range("A5:g15" ), 0)
z = Worksheetfunction.Match(Range("c3"),Range("A5:g15" ), 0)

Selection.Sort Key1:=Columns(x), Order1:=xlAscending, _
Header:=xlGuess,OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

Selection.Sort Key2:=Columns(y), Order1:=xlAscending, _
Header:=xlGuess,OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

Selection.Sort Key3:=Columns(z), Order1:=xlAscending, _
Header:=xlGuess,OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

Could anyone possibly help me figure out the proper syntax?...

Thanks for your help in advance.

Will

record a macro while manually using three sort keys and you will see
parameters you need to use.

Or look in help for the sort method of the range object.

It is all done in one sort command using Key1, Order1, Key2, Order2, Key3,
Order3

--
Regards,
Tom Ogilvy


Wilbur wrote in message
...
Hello All,

I'm trying to create a worksheet that has a database with the capability
to
dynamically set each of the three sort keys. My database is in the range
from a5:g15, the field names are in row 5. I've created in cell drop
down
lists using validation in cells a4, b4, c4. I'd like to use these
dropdowns
to select sort keys 1-3 respectively.

Searching google I've found some posts that have something similar with a
single sort key that was posted by Steve Bell:

Dim x as Integer

x = Worksheetfunction.Match(Range("A3"),Range("A5:g15" ), 0)

Selection.Sort Key1:=Columns(x), Order1:=xlAscending, _
Header:=xlGuess,OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

but I can't figure out how to modify it to accomodate three sort keys.
I've
tried dimensioning two other variables y and z for the other sort keys and
the pasting the above code with x changed to y and z respectively....no
luck, I keep getting errors.

This is what I have so far.

Dim x as Integer
Dim y as Integer
Dim z as Integer

x = Worksheetfunction.Match(Range("A3"),Range("A5:g15" ), 0)
y = Worksheetfunction.Match(Range("b3"),Range("A5:g15" ), 0)
z = Worksheetfunction.Match(Range("c3"),Range("A5:g15" ), 0)

Selection.Sort Key1:=Columns(x), Order1:=xlAscending, _
Header:=xlGuess,OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

Selection.Sort Key2:=Columns(y), Order1:=xlAscending, _
Header:=xlGuess,OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

Selection.Sort Key3:=Columns(z), Order1:=xlAscending, _
Header:=xlGuess,OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom

Could anyone possibly help me figure out the proper syntax?...

Thanks for your help in advance.

Will

Thanks Tom, your suggestions gave me the direction I needed to get this
wrapped up. To close the loop I'm posting my code in the hope that it'll be
useful to a future searcher. Portions of this code have been contributed or
drawn from other newsgroup postings...thanks to all that have helped..

The project has VB code that enables the selection of sort keys and
subsequent sorting of a database by selecting the sort keys from in cell
drop downs. this makes it possible to sort the database without having to
go through the normal excel menu structue. For whatever reason some of my
more novice co-workers can't seem to grasp the built in sorting functions
and selection of sort ranges :)

The project has two VB components. The first is this section of code placed
in the sheet code.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("e20")) Is Nothing Then threekeysort
End Sub

This code will run the "threekeysort" macro anytime cell e20 is selected.

The second component is the sorting subroutine named threekeysort. I placed
this in a regular code module. In a nut shell, this uses in cell lists
(using the menu command for Data:Validation). There are dropdowns in cells
B20, C20, D20, populated with values in cells A21:U21. All of this is at
the worksheet level, no VB.

The macro to tie it all together follows. I've tried to comment the code to
make it readable. I hope it helps.

Sub threekeysort()

' The Match function returns the index value of the item in range a15:u15
(field names)
' that match the value in b14, c14, or d14

my_sort_key1 = WorksheetFunction.Match(Range("b20"), Range("A21:u21"), 0)
my_sort_key2 = WorksheetFunction.Match(Range("c20"), Range("A21:u21"), 0)
my_sort_key3 = WorksheetFunction.Match(Range("d20"), Range("A21:u21"), 0)

' Turn off screen updating while the macro runs to give smoother updates
Application.ScreenUpdating = False



' Set the sort range, then perform the sort on the field values identified
by the three index keys
' The sort key is set as the column indicated by the result of
columns(my_sort_key1)

Range("a22:U554").Select
Selection.Sort key1:=Columns(my_sort_key1), Order1:=xlAscending,
key2:=Columns(my_sort_key2) _
, Order2:=xlAscending, key3:=Columns(my_sort_key3),
Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal, DataOption3:=
_
xlSortNormal

' Select the first sort key cell to deselect the entire sort range. this
gives a cleaner appearnace rather
' than keeping the entire sort range selected following the sort

Range("b20").Select

' Turn on screen updating

Application.ScreenUpdating = True

End Sub

Wilbur,

Looks like nice code! Glad my input started you off.

Never considered this approach before... Maybe I'll 'rip' it off!!!
--
sb
"Wilbur" wrote in message
...
Thanks Tom, your suggestions gave me the direction I needed to get this
wrapped up. To close the loop I'm posting my code in the hope that it'll
be
useful to a future searcher. Portions of this code have been contributed
or
drawn from other newsgroup postings...thanks to all that have helped..

The project has VB code that enables the selection of sort keys and
subsequent sorting of a database by selecting the sort keys from in cell
drop downs. this makes it possible to sort the database without having to
go through the normal excel menu structue. For whatever reason some of my
more novice co-workers can't seem to grasp the built in sorting functions
and selection of sort ranges :)

The project has two VB components. The first is this section of code
placed
in the sheet code.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("e20")) Is Nothing Then threekeysort
End Sub

This code will run the "threekeysort" macro anytime cell e20 is selected.

The second component is the sorting subroutine named threekeysort. I
placed
this in a regular code module. In a nut shell, this uses in cell lists
(using the menu command for Data:Validation). There are dropdowns in
cells
B20, C20, D20, populated with values in cells A21:U21. All of this is at
the worksheet level, no VB.

The macro to tie it all together follows. I've tried to comment the code
to
make it readable. I hope it helps.

Sub threekeysort()

' The Match function returns the index value of the item in range a15:u15
(field names)
' that match the value in b14, c14, or d14

my_sort_key1 = WorksheetFunction.Match(Range("b20"), Range("A21:u21"), 0)
my_sort_key2 = WorksheetFunction.Match(Range("c20"), Range("A21:u21"), 0)
my_sort_key3 = WorksheetFunction.Match(Range("d20"), Range("A21:u21"), 0)

' Turn off screen updating while the macro runs to give smoother updates
Application.ScreenUpdating = False



' Set the sort range, then perform the sort on the field values identified
by the three index keys
' The sort key is set as the column indicated by the result of
columns(my_sort_key1)

Range("a22:U554").Select
Selection.Sort key1:=Columns(my_sort_key1), Order1:=xlAscending,
key2:=Columns(my_sort_key2) _
, Order2:=xlAscending, key3:=Columns(my_sort_key3),
Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:=
_
xlSortNormal

' Select the first sort key cell to deselect the entire sort range. this
gives a cleaner appearnace rather
' than keeping the entire sort range selected following the sort

Range("b20").Select

' Turn on screen updating

Application.ScreenUpdating = True

End Sub

Wilbur,

Just realized - it is not necessary to select to do the sort...
Range("a22:U554").Sort.......................

And there are ways to make the range dynamic (if needed)...
--
sb
"Wilbur" wrote in message
...
Thanks Tom, your suggestions gave me the direction I needed to get this
wrapped up. To close the loop I'm posting my code in the hope that it'll
be
useful to a future searcher. Portions of this code have been contributed
or
drawn from other newsgroup postings...thanks to all that have helped..

The project has VB code that enables the selection of sort keys and
subsequent sorting of a database by selecting the sort keys from in cell
drop downs. this makes it possible to sort the database without having to
go through the normal excel menu structue. For whatever reason some of my
more novice co-workers can't seem to grasp the built in sorting functions
and selection of sort ranges :)

The project has two VB components. The first is this section of code
placed
in the sheet code.

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If Not Intersect(Target, Range("e20")) Is Nothing Then threekeysort
End Sub

This code will run the "threekeysort" macro anytime cell e20 is selected.

The second component is the sorting subroutine named threekeysort. I
placed
this in a regular code module. In a nut shell, this uses in cell lists
(using the menu command for Data:Validation). There are dropdowns in
cells
B20, C20, D20, populated with values in cells A21:U21. All of this is at
the worksheet level, no VB.

The macro to tie it all together follows. I've tried to comment the code
to
make it readable. I hope it helps.

Sub threekeysort()

' The Match function returns the index value of the item in range a15:u15
(field names)
' that match the value in b14, c14, or d14

my_sort_key1 = WorksheetFunction.Match(Range("b20"), Range("A21:u21"), 0)
my_sort_key2 = WorksheetFunction.Match(Range("c20"), Range("A21:u21"), 0)
my_sort_key3 = WorksheetFunction.Match(Range("d20"), Range("A21:u21"), 0)

' Turn off screen updating while the macro runs to give smoother updates
Application.ScreenUpdating = False



' Set the sort range, then perform the sort on the field values identified
by the three index keys
' The sort key is set as the column indicated by the result of
columns(my_sort_key1)

Range("a22:U554").Select
Selection.Sort key1:=Columns(my_sort_key1), Order1:=xlAscending,
key2:=Columns(my_sort_key2) _
, Order2:=xlAscending, key3:=Columns(my_sort_key3),
Order3:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal, DataOption2:=xlSortNormal,
DataOption3:=
_
xlSortNormal

' Select the first sort key cell to deselect the entire sort range. this
gives a cleaner appearnace rather
' than keeping the entire sort range selected following the sort

Range("b20").Select

' Turn on screen updating

Application.ScreenUpdating = True

End Sub