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...
Whe

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

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

On Monday, August 5, 2013 9:45:23 PM UTC-7, GS wrote:
Well, you can check the sheetname to make sure it's in the

gsSheetsToSort string, right?



--

Garry


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

On Monday, August 5, 2013 9:45:23 PM UTC-7, GS wrote:
Well, you can check the sheetname to make sure it's in the

gsSheetsToSort string, right?



--

Garry


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

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

On Tuesday, August 6, 2013 8:33:41 AM UTC-7, GS wrote:
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



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

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

On Tuesday, August 6, 2013 12:10:12 AM UTC-7, Claus Busch wrote:
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

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

On Tuesday, August 6, 2013 1:05:39 AM UTC-7, Howard wrote:
On Tuesday, August 6, 2013 12:10:12 AM UTC-7, Claus Busch wrote:

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



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

A typo, make that $ a 4 instead.

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2

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.
--
Win XP PRof SP2 / Vista Ultimate SP2
Office 2003 SP2 /2007 Ultimate SP2