Worksheet sorting code/technique advise

[Howard]

I offered this code to an OP, given his explanation that whenever his users leave a worksheet column B should be sorted. Then it turned out to be multiple columns that needed to be sorted (see .SetRange Range("B:R") in code).

That worked in my tests where col B through R would be sorted and col B was the "sort on col". I assume that is the default unless somehow coded differently, which I did not pursue.

Then the other shoe fell and it turns out that not every sheet in the workbook requires a sort, in fact some sheets should be excluded of any sort, and at the same time the columns to sort are different across the various sheets that require a sort.

Is there a common sense approach to this where the sheet needing NO sort can be excluded while at the same time those that require a sort can sanely be include and the columns to sort be identified?

Perhaps a Case Select...
Where:

Case = Sheet1
Do a sort on XYZ

Case = Sheet 25
Do a sort on ABC

Case Else
Don't do anything

End Case

Thanks>
Howard


Option Explicit

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

With ActiveSheet.Sort
'.SetRange Range("B:R")
.SetRange Range("B:B")
.Header = xlNo
'.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

End Sub

 

[GS]

Well, you can check the sheetname to make sure it's in the
gsSheetsToSort string, 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

 

[Howard]

Well, you can check the sheetname to make sure it's in the

gsSheetsToSort string, right?

Because of the large variance from sheet to sheet of what columns need sorting, say sort A, B, C, F on one sheet, then the next sheet is column B through AA need sorted, the next sheet different still, and the exclusion of some sheets altogether, I think I will attempt a select case and define for each sheet what to sort and the Case Else will be the ones excluded.

Maybe use the change event Sheet Deactivate to pop up an alert message to run the sort macro vbYes vbNo.

Do you see any major no-no's with that approach?

Howard

 

[Claus Busch]

Hi Howard,

Am Mon, 5 Aug 2013 16:30:47 -0700 (PDT) schrieb Howard:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)

with SheetDeactivate you will get a error message. Try another event.
What do you know about the sheet? Do you know the column that will be
sorted? Then you could try:

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim MyCol As Integer

Select Case Sh.Name
Case "Sheet1"
MyCol = 1
Case "Sheet2"
MyCol = 2
Case "Sheet3"
MyCol = 4
End Select
Sh.UsedRange.Sort key1:=Cells(1, MyCol), _
order1:=xlAscending, Header:=xlGuess
End Sub


Regards
Claus B.

 

[Howard]

Hi Howard,



Am Mon, 5 Aug 2013 16:30:47 -0700 (PDT) schrieb Howard:






with SheetDeactivate you will get a error message. Try another event.

What do you know about the sheet? Do you know the column that will be

sorted? Then you could try:



Private Sub Workbook_SheetActivate(ByVal Sh As Object)

Dim MyCol As Integer



Select Case Sh.Name

Case "Sheet1"

MyCol = 1

Case "Sheet2"

MyCol = 2

Case "Sheet3"

MyCol = 4

End Select

Regards

Claus B.

Hi Claus,

No I don't know the specific columns nor the sheet names. I was hoping to write some plain jane select case macro where the OP would only have to change the column ranges and sheet names to suit his workbook.

So with the snippet you posted if in Case "Sheet1" there were column 1, $, 7, 8, 12 that needed sorted what would MyCol = look like?

The reason I mentioned sheet deactivate is because the OP wanted to sort upon leaving the sheet. He may need to change his thinking in that regard.

I'm not sure what would be best to evoke the sorts. I'm open to suggestions.

Howard

 

[Howard]

Hi Claus,



No I don't know the specific columns nor the sheet names. I was hoping to write some plain jane select case macro where the OP would only have to change the column ranges and sheet names to suit his workbook.



So with the snippet you posted if in Case "Sheet1" there were column 1, $, 7, 8, 12 that needed sorted what would MyCol = look like?



The reason I mentioned sheet deactivate is because the OP wanted to sort upon leaving the sheet. He may need to change his thinking in that regard.



I'm not sure what would be best to evoke the sorts. I'm open to suggestions.



Howard

A typo, make that $ a 4 instead.

 

[Claus Busch]

Hi Howard,

Am Tue, 6 Aug 2013 01:10:14 -0700 (PDT) schrieb Howard:

A typo, make that $ a 4 instead.

can you do a test with your data?

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim MyCol As Variant
Dim i As Integer

Select Case Sh.Name
Case "Sheet1"
MyCol = Array(4, 7, 1, 8, 12)
Case "Sheet2"
MyCol = Array(2, 8)
Case "Sheet3"
MyCol = Array(1, 4, 7, 8, 12)
End Select
Sh.Sort.SortFields.Clear
For i = LBound(MyCol) To UBound(MyCol)
Sh.Sort.SortFields.Add Key:=Cells(2, MyCol(i)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
Next
With Sh.Sort
.SetRange Sh.UsedRange
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub


Regards
Claus B.

 

[Claus Busch]

Hi Howard,

Am Tue, 6 Aug 2013 11:09:14 +0200 schrieb Claus Busch:

For i = LBound(MyCol) To UBound(MyCol)
Sh.Sort.SortFields.Add Key:=Cells(2, MyCol(i)) _

if there are no headers Key has to be
Cells(1, MyCol(i)) and Headers=xlNo


Regards
Claus B.

 

[Howard]

if there are no headers Key has to be

Cells(1, MyCol(i)) and Headers=xlNo

Regards

With the code in a standard module and since there are no headers, the change as you mentioned to include .Header = xlNo, if I select a sheet, 1, 2, or 3, nothing happens. Isn't a sheet activated by selecting it?

Saved the workbook as macro enabled, closed and reopened workbook.

For i = LBound(MyCol) To UBound(MyCol)
Sh.Sort.SortFields.Add Key:=Cells(1, MyCol(i)) And Headers = xlNo _
, SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
Next
With Sh.Sort
.SetRange Sh.UsedRange
..Header = xlNo
'.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


Howard

 

[Claus Busch]

Hi Howard,

Am Tue, 6 Aug 2013 04:09:05 -0700 (PDT) schrieb Howard:

For i = LBound(MyCol) To UBound(MyCol)
Sh.Sort.SortFields.Add Key:=Cells(1, MyCol(i)) And Headers = xlNo _

^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
Sh.Sort.SortFields.Add Key:=Cells(1, MyCol(i)) _

, SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
Next
With Sh.Sort
.SetRange Sh.UsedRange
.Header = xlNo
'.Header = xlYes ^^^^^^^^^^^^^
Header=xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

sorry that I confused you :-(
Headers is under Sh.Sort

Try:
Sh.Sort.SortFields.Clear
For i = LBound(MyCol) To UBound(MyCol)
Sh.Sort.SortFields.Add Key:=Cells(1, MyCol(i)) _
, SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
Next
With Sh.Sort
.SetRange Sh.UsedRange
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With


Regards
Claus B.

 

[GS]

Because of the large variance from sheet to sheet of what columns need
sorting, say sort A, B, C, F on one sheet, then the next sheet is column B
through AA need sorted, the next sheet different still, and the exclusion of
some sheets altogether, I think I will attempt a select case and define for
each sheet what to sort and the Case Else will be the ones excluded.

Maybe use the change event Sheet Deactivate to pop up an alert message to run
the sort macro vbYes vbNo.

Do you see any major no-no's with that approach?

Howard

I would store the sort criteria/range info for each sheet in a defined
name (or named cell) that your procedure reads from so you don't need
to include the extra coding (and added overhead) that a Select Case
construct entails. This will greatly reduce ongoing code maintenance.

For example, in multi-sheet projects I store various UI settings for
each sheet as a defined name, and the associated setting in the
RefersTo property. The code is generic and so doesn't care what UI
settings it's procesing because each sheet has sheet-specific info the
procedure reads from and executes against that sheet. Now, in my
cAppEvents class module the UI settings are applied when the sheet is
activated in UserMode. When in DevMode the UI settings are removed so I
can work on the sheet without runtime restrictions in place. Then to
test I simply toggle the boolean that controls gbDevMode. When False
the project runs in UserMode. The trigger at startup is the existence
of a file in the project path folder that I don't distribute to users.
The toggle is controlled by a keyboard shortcut which I also don't
disclose.

Sorry for elaborating so extensively but felt it necessary to
explaining the scope of the sheet-specific settings and one aspect of
what you can do with this concept.

--
Garry

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

 

[GS]

Given that the sort is user controlled.., I think using a named cell
where the user can input a delimited list of col labels/nums would be
the easiest approach. Criteria could be included via a 2nd delimiter OR
input to it's own cell. I suggest an instruction to users to make the
delimited list in order of preferred sort key order...

2,1,3,4,5:a (sort ascending)
2,1,3,4,5:d (sort descending)

...where col 2 is used as the key for the sort, and the sort order is
specified by a 2nd delimiter. Your code can handle this as follows...

Dim vSortInfo, vCols, vSortOrder
...
...
vSortInfo = Split([SortCriteria], ":")
vCols = Split(vSortInfo(0), ",")
If vSortInfo(1) = "a" Then vSortOrder = xlAscending _
Else vSortOrder = xlDescending
...
...

...where vCols can be the sort range passed as an array. The rest should
be self-explanatory but I'll elaborate if need be!

--
Garry

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

 

[Howard]

sorry that I confused you :-(

Headers is under Sh.Sort



Try:

Sh.Sort.SortFields.Clear

For i = LBound(MyCol) To UBound(MyCol)

Sh.Sort.SortFields.Add Key:=Cells(1, MyCol(i)) _

, SortOn:=xlSortOnValues, Order:=xlAscending, _

DataOption:=xlSortNormal

Next

With Sh.Sort

.SetRange Sh.UsedRange

.Header = xlNo

.MatchCase = False

.Orientation = xlTopToBottom

.SortMethod = xlPinYin

.Apply

End With

Hi Claus,

Okay got it, after a reread I see what you meant now.

Made that change, but I cannot get the sheet to "Activate" and fire the code.

Doesn't clicking on the sheet tab activate the sheet.

I've tried it in the ThisWorkbook module, sheet module and is in standard Module 1 at present.

I like the

Case "Sheet1"
MyCol = Array(4, 7, 1, 8, 12)

where I assume the first column, 4 in this Case, will be the sort key. And the Order= in the For I=.

Howard

 

[Claus Busch]

Hi Howard,

Am Tue, 6 Aug 2013 09:54:09 -0700 (PDT) schrieb Howard:

Made that change, but I cannot get the sheet to "Activate" and fire the code.

Doesn't clicking on the sheet tab activate the sheet.

for me it works fine. Please download here:
https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326
the workbook "Sort".
This Workbook has headers but the handling is the same.
The code belongs to the code module of ThisWorkbook.


Regards
Claus B.

 

[Howard]

Given that the sort is user controlled.., I think using a named cell

where the user can input a delimited list of col labels/nums would be

the easiest approach. Criteria could be included via a 2nd delimiter OR

input to it's own cell. I suggest an instruction to users to make the

delimited list in order of preferred sort key order...



2,1,3,4,5:a (sort ascending)

2,1,3,4,5:d (sort descending)



..where col 2 is used as the key for the sort, and the sort order is

specified by a 2nd delimiter. Your code can handle this as follows...



Dim vSortInfo, vCols, vSortOrder

...

...

vSortInfo = Split([SortCriteria], ":")

vCols = Split(vSortInfo(0), ",")

If vSortInfo(1) = "a" Then vSortOrder = xlAscending _

Else vSortOrder = xlDescending

...

...



..where vCols can be the sort range passed as an array. The rest should

be self-explanatory but I'll elaborate if need be!

Hi Gary,
That seems pretty good to me, if I understand correctly.

User enters the columns in a specific cell AND order, minus the (sort ascending/descending), where the order is really only concerned with the FIRST column which will be the sort key.

Question: Is that cell then named in the sheet/workbook as "vSortInfo"? (no quotes)

2,1,3,4,5:a (sort ascending)
2,1,3,4,5:d (sort descending)

As I mentioned to Claus, I cannot get the code to run from an Activate action.
Not sure what I'm doing wrong there.

Howard

 

[Howard]

Hi Howard,



Am Tue, 6 Aug 2013 09:54:09 -0700 (PDT) schrieb Howard:






for me it works fine. Please download here:

https://skydrive.live.com/#cid=9378AAB6121822A3&id=9378AAB6121822A3!326

the workbook "Sort".

This Workbook has headers but the handling is the same.

The code belongs to the code module of ThisWorkbook.





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

Okay Claus, I'll give it a go and move the code back to the ThisWorkbook module.

Thanks,
Howard

 

[GS]

Given that the sort is user controlled.., I think using a named cell

where the user can input a delimited list of col labels/nums would be

the easiest approach. Criteria could be included via a 2nd delimiter OR

input to it's own cell. I suggest an instruction to users to make the

delimited list in order of preferred sort key order...



2,1,3,4,5:a (sort ascending)

2,1,3,4,5:d (sort descending)



..where col 2 is used as the key for the sort, and the sort order is

specified by a 2nd delimiter. Your code can handle this as follows...



Dim vSortInfo, vCols, vSortOrder

...

...

vSortInfo = Split([SortCriteria], ":")

vCols = Split(vSortInfo(0), ",")

If vSortInfo(1) = "a" Then vSortOrder = xlAscending _

Else vSortOrder = xlDescending

...

...



..where vCols can be the sort range passed as an array. The rest should

be self-explanatory but I'll elaborate if need be!

Hi Gary,
That seems pretty good to me, if I understand correctly.

User enters the columns in a specific cell AND order, minus the (sort
ascending/descending), where the order is really only concerned with the
FIRST column which will be the sort key.

The sort order was/is optional, just so I could show how to process it
if stored along with cols lists.

Question: Is that cell then named in the sheet/workbook as "vSortInfo"? (no
quotes)

If you mean the defined named of the cell containing the sort criteria,
then in my code it's between the []! Name is "SortCriteria" and it has
local scope so it can be reused on all sheets.

2,1,3,4,5:a (sort ascending)
2,1,3,4,5:d (sort descending)

As I mentioned to Claus, I cannot get the code to run from an Activate
action. Not sure what I'm doing wrong there.

If using an event then use the event's ref to the sheet...

vSortInfo = Split(sh.[SortCriteria], ":")

...but since you're not using the sort order then just use the value
stored...

Dim sColsToSort$ 'as string
sColsToSort = sh.[SortCriteria]

...which, using my previous example, returns the delimited string
"2,1,3,4,5" of the cols to be sorted.

--
Garry

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

 

[Howard]

This Workbook has headers but the handling is the same.

The code belongs to the code module of ThisWorkbook.

Hi Claus,

Moved the code to the ThisWorkbook module and it fires each time a sheet tab is selected.

A puzzling aspect is now, it sorts EVERY column on the sheet despite having say

Case "Sheet1"
MyCol = Array(1, 2, 3, 4, 5)

It sorts 6 on to whatever has data in it. I'm using about 10 to 12 columns in my tests.

Near as I can tell the only thing the code obeys is the first column as the "sort on" column.

I put in 1 to 9 in column A and pulled that over to column P, and manually toggle back and forth to Ascending/Descending in the code and it sorts A through P each time sheet is selected even though 1 to 5 are in the Array.

So it seems the array does not exclude any column after all. It really only sets the "sort on" column.

Howard

 

[Claus Busch]

Hi Howard,

Am Tue, 6 Aug 2013 12:14:20 -0700 (PDT) schrieb Howard:

A puzzling aspect is now, it sorts EVERY column on the sheet despite having say

Case "Sheet1"
MyCol = Array(1, 2, 3, 4, 5)

after activating the sheet select a cell into the table and select Data
=> Sort and you see that the only sort keys are the keys in the array.
The data sets will not be destroyed. So it looks like the whole sheet
has been sorted.


Regards
Claus B.

 

[GS]

Ok, my understanding is that you want to sort each sheet's entire data
as per the sort order keys. Now I get the impression you want to sort a
list of columns *individually* as an array separate from the rest of
the sheet data. Which is it?

--
Garry

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