Sort data when deactivating worksheet

[EdwardToet]

Good evening all (It's that time of day where I struggle with VBA),

I have a problem that has been discussed a number of times, also on this forum, but I cannot get things to work. I need my project to sort the data on each worksheet by date automatically when I leave the sheet. If the data is not sorted in this way, the summary sheet does not add up the figures correctly. After consulting various forums on the subject, including this one, I came up with the code for:
Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
followed by the sorting instruction.
This works, but not on leaving the sheet. The sorting is only done when I re-enter the sheet, thus requiring extra keystrokes. Does anybody have any idea what I may be doing wrong. I regret that I am unable to give more information as I do not (yet) know how to squeeze more words into the restricted space of this input form.

 

[Smitty]

Welcome to the Board!

This works fine for me:

<font face=Calibri><SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_SheetDeactivate(<SPAN style="color:#00007F">ByVal</SPAN> Sh <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Object</SPAN>)<br>    <SPAN style="color:#00007F">With</SPAN> ActiveWorkbook.ActiveSheet.Sort<br>        .SortFields.Clear<br>        .SortFields.Add Key:=Range("A1:A8"), _<br>        SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:=xlSortNormal<br>        .SetRange Range("A1:A8")<br>        .Header = xlGuess<br>        .MatchCase = <SPAN style="color:#00007F">False</SPAN><br>        .Orientation = xlTopToBottom<br>        .SortMethod = xlPinYin<br>        .Apply<br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

Where did you put the code?

 

[EdwardToet]

Thanks for your reply, Smitty.

My code was stored in ThisWorkbook, under Microsoft Excel Objects, the same place where I pasted your code (after having removed my code). In your code I only changed the Range references to suit my needs. Alas, with your code, when I click a different sheet tap, Bill Gates tells me that I have Runtime error '438': Object doesn't support this property or method (vintage Excel version, perhaps? Mine is 2002). Code execution hangs up at the first line of code, the line with the "With" statement.
Anyway, sorting is not the problem. The problem appears to be that with the Workbook_SheetDeactivate (or Worksheet_Deactivate) sub routine the action does not take place when leaving the sheet, but when re-entering it. I have tried, however, to let a Message Box pop up when leaving a sheet (with the same subroutine) and this works fine! Any thoughts on this?
Edward (the one with the puzzled look on his face).

 

[EdwardToet]

Smitty, the code I was trying to implement (stored in This Workbook) is as follows:

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
' Sort only sheets with an entry in cell A10
    If Range("A10") <> 0 Then
'Sort Column A first, then column D, starting with Row 10
    Application.ScreenUpdating = False
    [A10].CurrentRegion.Select
    Selection.Sort Key1:=Range("A10"), Order1:=xlAscending,    Key2:=Range("D10") _
        , Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, DataOption2 _
        :=xlSortNormal
' Bring first empty row in view to allow further data entry when re-entering sheet
    Range("A10").End(xlDown).Offset(1, 0).Select
    Application.Goto Reference:=ActiveCell.Offset(-35, 0), Scroll:=True
    End If
 End Sub

Regards, Edward

 

[VoG]

Try like this

Private Sub Workbook_SheetDeactivate(ByVal Sh As Object)
With Sh
    .UsedRange.Sort Key1:=.Range("A2"), Order1:=xlAscending, _
    Header:=xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTop
End With
End Sub

 

[Smitty]

Sorry, my code was for 2007+, so your version won't support it.

I think what Peter posted should work for you though, you'll just need to add you 2nd key.

Note where he removed selecting, which is the most likely culprit.

 

[EdwardToet]

Hi Smitty and VoG,
VoG's code also didn't work correctly for me. However, I have made some changes in my project, in that I made my summary sheet independent of the sorting order in the other sheets. A lot of work, but it paid out and has some other advantages too. What a joy if your code works flawlessly. That gives you the same elated feeling that you get from the sound of a golfball when you hit it exactly right. Still, I can't stand that I could not solve the sheet de-activation issue, but for the time being my problem is solved. Thanks for your support guys!

Edward

 

[The Dutchman]

Re: Sort named range when deactivating worksheet

x