Page 1 of 2 12 LastLast
Results 1 to 15 of 19
  1. #1
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Sorting in chronological order (Excel 2003)

    Hello loungers & belated Happy New Year.....can someone help me with some code that will sort data in chronological order. I am trying to do it as a worksheet change type of event that will happen automatically, rather than using DATA-SORT. I am attaching a sample of the workbook/sheet. It is an expense claim form and I have started to enter some sample data. The 'sort' needs to be by date, into chronological order. I have some code that I am using in another workbook, but the 'dates' in that other workbook are simply entered as 1 or 5 or 23 etc etc (rather than Feb 1, 2009, Feb 5, 2009 or March 23, 2009). The actual workbook has 4 sheets to it and I will be content just to have each sheet sort itself chronologically (ie: I am not trying to sort data to the point where data would move, for example, from page 2 to page 3, just to be in chronological order. Hope this is a clear enough explanation...thanks for any help you can suggest....ps: and if the easiest way to do it is with a macro and a 'button' that executes the macro, that would work nicely too, b/c I would simply add it to each sheet or have it execute a sort on all sheets in sequence.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting in chronological order (Excel 2003)

    You have set a password on the VBA project...

  3. #3
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Sorting in chronological order (Excel 2003)

    Thanks...I sort of wondered if I had forgotten to undo it but wasn't sure....now, I can't remember how/where I go to undo it...I know it is in Porperties, but where can I find that? I am ataching another copy of the same workbook and I think it might be undone (no guarantees..) but if not, can you tell me how to get to where I need to go to undo the passsowrd? Thank, Hans
    Attached Files Attached Files

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting in chronological order (Excel 2003)

    The VBA code is still protected.
    To get rid of this protection:

    Open the workbook.
    Activate the Visual Basic Editor.
    Select Tools | ExpenseSheets Properties...
    Enter the password, then click OK.
    Activate the Security tab.
    Clear the check box and both text boxes.
    Click OK.
    Save the workbook.

  5. #5
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Sorting in chronological order (Excel 2003)

    OK Hans.,...I have now removed the password...sorry about that
    Attached Files Attached Files

  6. #6
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting in chronological order (Excel 2003)

    Place the following code in a standard module<div style="width: 100%; background-color: #FFFFFF;"><code><font color=black> <font color=blue>Sub</font color=blue> SortDate(Target <font color=blue>As</font color=blue> Range)
    <font color=448800>'</font color=448800>
    <font color=blue>Dim</font color=blue> rngDate <font color=blue>As</font color=blue> Range
    <font color=blue>Set</font color=blue> rngDate = Range("B6:B25")
    <font color=blue>If</font color=blue> Intersect(Target, rngDate) <font color=blue>Is</font color=blue> <font color=blue>Nothing</font color=blue> <font color=blue>Then</font color=blue>
    Range("B" & Target.Row, "Q" & Target.Row) _
    .Interior.ColorIndex = xlNone
    <font color=blue>Exit</font color=blue> <font color=blue>Sub</font color=blue>
    <font color=blue>Else</font color=blue>
    Range("B" & Target.Row, "Q" & Target.Row) _
    .Interior.ColorIndex = 6
    Range("B6:Q25").Sort Key1:=Range("B6"), _
    Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTopToBottom, _
    DataOption1:=xlSortNormal
    <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    End <font color=blue>Sub</font color=blue>

    </font color=black></code></div hiblock>

    And the following code in each Worksheet

    <div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Private</font color=blue> <font color=blue>Sub</font color=blue> Worksheet_Change(<font color=blue>ByVal</font color=blue> Target <font color=blue>As</font color=blue> Range)
    <font color=blue>Call</font color=blue> SortDate(Target)
    <font color=blue>End</font color=blue> <font color=blue>Sub</font color=blue>


    </font color=black></code></div hiblock>
    Regards
    Don

  7. #7
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Sorting in chronological order (Excel 2003)

    Hi Don...does "in a standard module" mean that I put it in the VIEW CODE part of the workbook, or do I juts put it in the regular portion where macros are stored and then in each worksheet (as VIEW CODE) I put iun the second part of the code yu provided?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting in chronological order (Excel 2003)

    The latter...

  9. #9
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Sorting in chronological order (Excel 2003)

    Thank you, Don and Hans....early testing looks good, but I am curious as to why you used code that colors the row yellow?

  10. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting in chronological order (Excel 2003)

    The user will enter or change a date, which may well relocate that record. Highlighting the record will allow the user to quickly identify where to continue entering the data. When any data is entered in any field of that record other than the date, the highlighting is removed. If you wish the code could be revised to remove the highlighting after some seconds or part of a second.
    Regards
    Don

  11. #11
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Sorting in chronological order (Excel 2003)

    Ahh...now I see...thank you for that added feature....

  12. #12
    4 Star Lounger
    Join Date
    Oct 2005
    Posts
    599
    Thanks
    1
    Thanked 1 Time in 1 Post

    Re: Sorting in chronological order (Excel 2003)

    Hi Don...thank you for that. You're correct about it being more user-friendly re: the highlighting, but when I use it in my actual workbook (see attachment) I get a lot of errors. I use a password ('open') and have added code to unprotect/protect with the password, but I don't think that is the problem......the earlier code you wrote (with the yellow highlighting) works nicely but your most recent code is easier to use, b/c it focuses attention on the shaded line and the user doesn't have to worry about 'making the yellow go away'.....in the attached sample, you'll see an error triggered whenever you enter something in columns D-Q....any ideas? Have I go the password unprottect/protect code in the wrong spot, maybe?
    Attached Files Attached Files

  13. #13
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Sorting in chronological order (Excel 2003)

    Congratulations on becoming a BronzeLounger (1200 posts), by the way! <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>

  14. #14
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting in chronological order (Excel 2003)

    <P ID="edit" class=small>(Edited by wdwells on 23-Jan-09 20:12. Code revised in response to bug identified at post 754817)</P>Hello David
    You should find the following code a little more user friendly than the previous version.<div style="width: 100%; background-color: #FFFFFF;"><code><font color=black><font color=blue>Option</font color=blue> <font color=blue>Explicit</font color=blue>
    <font color=blue>Sub</font color=blue> SortDate(Target <font color=blue>As</font color=blue> Range)
    <font color=448800>'</font color=448800>
    <font color=blue>Dim</font color=blue> TD <font color=blue>As</font color=blue> <font color=blue>Date</font color=blue> ' Date of the Target cell
    <font color=blue>Dim</font color=blue> rngDate <font color=blue>As</font color=blue> Range
    Dim lngRow As <font color=blue>Long</font color=blue>
    <font color=blue>Set</font color=blue> rngDate = Range("B6:B25")
    <font color=blue>If</font color=blue> <font color=blue>Not</font color=blue> Intersect(Target, rngDate) <font color=blue>Is</font color=blue> <font color=blue>Nothing</font color=blue> <font color=blue>Then</font color=blue>
    <font color=blue>On</font color=blue> <font color=blue>Error</font color=blue> <font color=blue>GoTo</font color=blue> ErrorHandler
    TD = Target
    <font color=448800>'Colour the target cell</font color=448800>
    Range("B" & Target.Row, "B" & Target.Row) _
    .Interior.ColorIndex = 6
    <font color=448800>'Sort the Data entry area</font color=448800>
    Range("B6:Q25").Sort Key1:=Range("B6"), _
    Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, _
    Orientation:=xlTop<font color=blue>To</font color=blue>Bottom, _
    DataOption1:=xlSortNormal
    <font color=448800>'Find the new location of the target</font color=448800>
    <font color=blue>For</font color=blue> lngRow = 6 To 25
    <font color=blue>If</font color=blue> Range("B" & lngRow) = TD _
    And Range("B" & lngRow).Interior.ColorIndex = 6 <font color=blue>Then</font color=blue>
    Range("B" & lngRow).Interior.ColorIndex = xlNone
    Range("B" & lngRow & ":Q" & lngRow).Select
    <font color=blue>Exit</font color=blue> <font color=blue>For</font color=blue>
    <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    <font color=blue>Next</font color=blue> lngRow
    <font color=blue>End</font color=blue> <font color=blue>If</font color=blue>
    <font color=blue>Exit</font color=blue> <font color=blue>Sub</font color=blue>
    ErrorHandler:
    MsgBox "Only ""Dates"" (without a time component), " & _
    "may be entered in this cell.", vbCritical
    Application.Undo
    <font color=blue>End</font color=blue> <font color=blue>Sub</font color=blue></font color=black></code></div hiblock>
    Regards
    Don

  15. #15
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Sorting in chronological order (Excel 2003)

    <big>Oops!</big>
    The problem you have found is a consequence of entering something other than a Date. The code at <!post=post 754745 ,754745>post 754745 <!/post> has been corrected to:
    1. <LI>Allow this in other than the Date column; and
      <LI>Advise the user of the violation and undo it if within the Date column.
    Regards
    Don

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •