IF you care to, here is a link to my workbook

Some reason it just wont fly for me. No errors and no sort.

All sheets are set up the same

Sort orders are in cell XFD1

https://www.dropbox.com/s/b0jdyo6kj5...p%20Box.xls m

Howard

Hi Howard,

Am Wed, 7 Aug 2013 03:22:19 -0700 (PDT) schrieb Howard:

Sort orders are in cell XFD1

delete the quotes at the sort orders

https://www.dropbox.com/s/b0jdyo6kj5...p%20Box.xls m
vSortCriteria is in workbook event and in module1. So you have to
declare it PUBLIC at the start of module1

Option Explicit
Public vSortCriteria As Variant

Sub SortCols(Wks As Worksheet, SortCriteria)
' Sorts individual specified cols
' Args: Wks The worksheet to be sorted
' SortCriteria Delimited string of col labels
' Not case sensitive
' **Note that SortCriteria is multi-delimited
' where sort order is delimited by a colon,
' and col labels by a comma. Left side of colon
' gets sorted ascending; right side descending.
' Examples: sort ascending only: "a,b,c,d,e:"
' sort descending only: ":a,b,c,d,e"
' sort both: "a,b,c:d,e"

Dim vCols, vSortOrder, v, bOrderBoth As Boolean
etc.
etc.
etc.



Regards
Claus B.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

vSortCriteria is in workbook event and in module1. So you have to
declare it PUBLIC at the start of module1

That's not true, IMO! Doing so would require resetting it before or
after each time the event fires. As is, it self initializes to Empty
and remains so until populated with data from Range("SortCriteria").
IOW, it serves as a test mechanism in the event procedure and on
success it passes its string value to SortCols().

It appears again in SortCols in a different context because of its
intended use. The fact that it happens to be the same name as the one
in the event is purely by attrition.

IMO, there's nothing wrong with your suggestion in context of a
project! My intent for the sort routine is that it be generic so it can
be reused without dependancies. A global var is a dependancy in the
context of your suggestion!

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

IF you care to, here is a link to my workbook

Some reason it just wont fly for me. No errors and no sort.

All sheets are set up the same

Sort orders are in cell XFD1

https://www.dropbox.com/s/b0jdyo6kj5...p%20Box.xls m

Howard

Ok.., as Claus suggests, remove the quotes from your SortCriteria
ranges. (I'm not sure why they're there to begin with!)

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Ok.., as Claus suggests, remove the quotes from your SortCriteria

ranges. (I'm not sure why they're there to begin with!)

Removed the quotes, WORKS FINE!!

The quotes were there due to my error. Saw quotes in the narrative some where and made a wrong assumption.

Sorry for all the fuss it has caused.

I consider this as a done deal, never thought it would evolve to be the very versatile version that it is.

I'll have to parse this thread and take a look at your suggestion if inputbox's
for column sort orders etc.

Many thanks to you and Claus for making it happen.

Where ever the code goes from me, it will have both your names attached.

I often say something like "Thanks to Garry/Claus of MS Excel Forum".

Hope you don't mind.

Regards,
Howard

I don't mind being mentioned as code source credit. I appreciate the
feedback, though, and glad you got it working. I figured the issue was
the very late hour you were still going at it. Happens to me more than
I can count.

So then.., you would have no problem adding SortRows() to your arsenal
of reusable procedures by simply modifying the SortCols() routine!
Right??

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

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

On Wednesday, August 7, 2013 11:20:01 AM UTC-7, GS wrote:
I don't mind being mentioned as code source credit. I appreciate the

feedback, though, and glad you got it working. I figured the issue was

the very late hour you were still going at it. Happens to me more than

I can count.



So then.., you would have no problem adding SortRows() to your arsenal

of reusable procedures by simply modifying the SortCols() routine!

Right??


Strange of you to mention that. I just minutes ago poster to the OP with the just finished workbook link and did indeed make a note that it does not sort rows.

I occurred to me, but I felt compelled to "let the dust settle" before mentioning it.


It really does make good sense to be able to do both, I think. And within the same code you have just written. Yes, indeed.

Howard

On Wednesday, August 7, 2013 11:20:01 AM UTC-7, GS wrote:
I don't mind being mentioned as code source credit. I appreciate the

feedback, though, and glad you got it working. I figured the issue was

the very late hour you were still going at it. Happens to me more than

I can count.



So then.., you would have no problem adding SortRows() to your arsenal

of reusable procedures by simply modifying the SortCols() routine!

Right??


Strange of you to mention that. I just minutes ago poster to the OP with the
just finished workbook link and did indeed make a note that it does not sort
rows.

I occurred to me, but I felt compelled to "let the dust settle" before
mentioning it.


It really does make good sense to be able to do both, I think. And within
the same code you have just written. Yes, indeed.

Howard

My thoughts are to load a userform in the event code that depicts rows
(if any) and/or cols (if any) with existing sort criteria for both
already loaded into textboxes for editing. This could also be used as a
preferences/settings/options dialog with that data stored on a hidden
sheet.

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

My thoughts are to load a userform in the event code that depicts rows

(if any) and/or cols (if any) with existing sort criteria for both

already loaded into textboxes for editing. This could also be used as a

preferences/settings/options dialog with that data stored on a hidden

sheet.

Garry


Anxious to see it. Looks like new ground again for me. Userform/textbox stuff.

Done a little but largely a mystery to me.

Howard