Sort data when deactivating worksheet

E

EdwardToet

New Member
Joined
Dec 30, 2010
Messages
4
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.
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
Sort by date Sort by votes
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?
 
Upvote 0
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).
 
Upvote 0
Smitty, the code I was trying to implement (stored in This Workbook) is as follows:

Code: 
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
 
Last edited by a moderator:
Upvote 0
Try like this

Code: 
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

GMC The Macro Man
  • Question Question
Sorting a table in a protected sheet when some cells are locked -MACRO needed
Replies
3
Views
425
RoryA
RoryA
S
  • Question Question
Auto Sorting League Table
Replies
2
Views
684
arun_eagertolearn
A
R
  • Question Question
Auto populate data from master worksheet to cooresponding .monthly spreadsheet
Replies
2
Views
359
arun_eagertolearn
A
M
  • Solved
Insert new row with incremental formula
Replies
8
Views
686
Sheik
S
J
  • Solved
Speeding up VBA
Replies
9
Views
242
jvoss
J

Forum statistics

Threads
1,224,504
Messages
6,179,142
Members
452,892
Latest member
JUSTOUTOFMYREACH

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top