Worksheet sorting code/technique advise

  • Thread starter Thread starter Howard
  • Start date Start date
Correct! This gives the user easy access for editing. This can be a

hidden column that requires dialog access (InputBox perhaps).

Otherwise, if you want to restrict editing or keep the sheet 'clean'

then use a defined name, which optionally can be edited via a dialog.

You could even precede the sorting with a userform that informs the

user that cols need sorting, displaying a list of col labels and their

sort order. The MsgBox can ask if the sort is to be done with current

settings or solicit new settings. If new settings are entered then

update the named range (or defined name's RefersTo) before calling

SortCols().



Just some food for thought if you want to make the project a bit more

robust!



--

Garry



Free uenet access at http://www.eternal-september.org

Classic VB Users Regroup

comp.lang.basic.visual.misc

microsoft.public.vb.general.discussion
Click to expand...

One hurdle to overcome before I play with that last suggestion, which by the way seems pretty keen.

Got the cells named for four sheets, in the Name Manager looks like this:

Name SortCriteria
Value "e,f:g,h"
Refers To =Sheet2!$XFD$1
Scope Sheet2

(Other three are same-same relative to their sheet)

When I run the code (by selecting a sheet) I get a Compile Error
ByRef argument Type Mismatch.

The vSortCriteria (last word in this next line is blue highlighted) Which is the last line in the sheet activate code.

If Not vSortCriteria = "" Then Call SortCols(Sh, vSortCriteria)

Did I name the sheet cell incorrectly or is this something else?

Howard
 
Ah! No.., remove the $ symbol from the associated arg in Sub SortCols.
In fact, you can remove the ByVal part too since we only ref the var.

Also change these lines in the SortCols() sub...

If vSortCriteria(0) = Empty Then _
bOrderBoth = False: vSortOrder = xlDescending: GoTo SortU
If vSortCriteria(1) = Empty Then _
bOrderBoth = False: vSortOrder = xlAscending: GoTo SortL

...so we won't use the LBound/UBound properties but ref the appropriate
element by index instead.

Note that omitting the coloon in the string will cause the sub to end
after checking the elements for Empty, meaning no sort takes place.

--
Garry

Free uenet access at http://www.eternal-september.org
Classic VB Users Regroup
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion
 
Here's what I have now where I think I followed you instructions...

I get the error with this entire sub heading blued out.
Private Sub Workbook_SheetActivate(Sh As Object)

"procedure declaration does not match description of event or procedure having same name"



Private Sub Workbook_SheetActivate(Sh As Object)
'Sub xx()
'//bno = maybe a msgbox .. sort or not sort? vbno = exit sub
Dim vSortCriteria
On Error Resume Next '//if name doesn't exist
vSortCriteria = Sh.Range("SortCriteria").Value
If Not vSortCriteria = "" Then Call SortCols(Sh, vSortCriteria)
End Sub

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 vSortCriteria, vCols, 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.Columns(v).Sort Key1:=Wks.Cells(1, v), _
Order1:=vSortOrder, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next 'v
If Not bOrderBoth Then Exit Sub

SortU:
If bOrderBoth Then vSortOrder = xlDescending
For Each v In Split(vSortCriteria(1), ",")

Wks.Columns(v).Sort Key1:=Wks.Cells(1, v), _
Order1:=vSortOrder, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next 'v
End Sub
 
I'm not getting any errors! For clarity...

In ThisWorkbook:
Option Explicit

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)
End Sub

In a standard module:
Option Explicit

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 vSortCriteria, vCols, 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.Columns(v).Sort Key1:=Wks.Cells(1, v), _
Order1:=vSortOrder, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next 'v
If Not bOrderBoth Then Exit Sub

SortU:
If bOrderBoth Then vSortOrder = xlDescending
For Each v In Split(vSortCriteria(1), ",")
Wks.Columns(v).Sort Key1:=Wks.Cells(1, v), _
Order1:=vSortOrder, Header:=xlNo, _
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Next 'v
End Sub

--
Garry

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

Am Wed, 7 Aug 2013 03:22:19 -0700 (PDT) schrieb Howard:
Sort orders are in cell XFD1
Click to expand...

delete the quotes at the sort orders
https://www.dropbox.com/s/b0jdyo6kj52f7ur/Sort Multiple columns Garry Drop Box.xlsm
Click to expand...
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.
 
vSortCriteria is in workbook event and in module1. So you have to
declare it PUBLIC at the start of module1
Click to expand...

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/b0jdyo6kj52f7ur/Sort Multiple columns Garry Drop Box.xlsm

Howard
Click to expand...

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!)
Click to expand...

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
 
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??
Click to expand...

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
 
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
Click to expand...

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.
Click to expand...
Garry
Click to expand...

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

Done a little but largely a mystery to me.

Howard
 
My thoughts are to load a userform in the event code that depicts rows
Anxious to see it. Looks like new ground again for me. Userform/textbox
stuff.

Done a little but largely a mystery to me.

Howard
Click to expand...

I've already started! I'll email you the project when it's done but
don't be in a rush because it's happening in my spare time...

--
Garry

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