Ok.., here ya go!
Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim vSortCriteria
On Error Resume Next '//if name doesn't exist
vSortCriteria = Sh.Range("SortCriteria").Value
' If Not vSortCriteria = Empty Then Call SortCols(Sh, vSortCriteria)
If Not vSortCriteria = Empty Then Call SortRows(Sh, vSortCriteria)
End Sub
Sub SortRows(Wks As Worksheet, SortCriteria)
' Sorts individual specified rows
' Args: Wks The worksheet to be sorted
' SortCriteria Delimited string of row nums
' **Note that SortCriteria is multi-delimited
' where sort order is delimited by a colon,
' and row nums by a comma. Left side of colon
' gets sorted ascending; right side descending.
' Examples: sort ascending only: "2,3,4,5:"
' sort descending only: ":2,3,4,5"
' sort both: "2,3:4,5"
Dim vSortCriteria, vSortOrder, v, bOrderBoth As Boolean
'Assume both sort orders
bOrderBoth = True
'Determine sort order
vSortCriteria = Split(SortCriteria, ":")
If vSortCriteria(0) = Empty Then _
bOrderBoth = False: vSortOrder = xlDescending: GoTo SortU
If vSortCriteria(1) = Empty Then _
bOrderBoth = False: vSortOrder = xlAscending: GoTo SortL
SortL:
If bOrderBoth Then vSortOrder = xlAscending
For Each v In Split(vSortCriteria(0), ",")
Wks.Rows(v).Sort Key1:=Wks.Cells(v, 1), _
Order1:=vSortOrder, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
Next 'v
If Not bOrderBoth Then Exit Sub
SortU:
If bOrderBoth Then vSortOrder = xlDescending
For Each v In Split(vSortCriteria(1), ",")
Wks.Rows(v).Sort Key1:=Wks.Cells(v, 1), _
Order1:=vSortOrder, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlLeftToRight, _
DataOption1:=xlSortNormal
Next 'v
End Sub
All that needed is a mechanism to store both row/col sort criteria
where both might be used on the same sheet. I'd go with using defined
names (SortCriteriaR, SortCriteriaC) or a row above the data area where
the cols used are also outside the data area. Using defined names
avoids col.count issues when running later files in earlier apps. The
approach to reading/writing these is a bit more complex but trivial
nevertheless. This is where using a dialog to manage both would
shine!<g
--
Garry
Free uenet access at
http://www.eternal-september.org
Classic
VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.
vb.general.discussion