Results 1 to 5 of 5
  1. #1
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts

    Sorting from a Command Button

    I have a workbook with a dozen or so worksheets. On the Finances worksheet, I have four Command Buttons that sort a table on four different columns. Things work OK, but I need to make some improvements that I'm not sure how to do. The table looks like this:

    FinancesWorksheet.JPG

    The buttons sort everything in the column immediately below them. As you can see, there are many identical entries in the three Date columns. Ideally, when a column is sorted by one of the dates, all the items with the same date would be sorted alphabetically by ID. The "Sort by ID" button does that, but the others don't. I often sort by ID before sorting by one of the dates. That gives me what I want in the Date Rcvd and Deposit Date columns, but it doesn't work with the Big Jackpots Deposit Date column. There, the items with the same date are sorted by Check#.

    The code for all this is in a Module for the workbook. Here it is, with the code for the leftmost button at the top, then moving button-by-button to the right:

    Code:
    Sub SortReceiptsByDateReceived()
    ' SortReceiptsByDateReceived Macro
    ' Macro recorded 6/27/2013 by Rankin Desktop
    ' Modified on 1/4/15 to accommodate changes in the Finances worksheet
    ' On the Finances page, sorts the Receipts entries by the date they were received
    ' IT REALLY SHOULD FIRST SORT EVERYTHING BY ID
    '
        Range("C9").Select
        Range("C8:P65536").Sort Key1:=Range("C8"), Order1:=xlAscending, Header:= _
            xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortTextAsNumbers
    End Sub
    
    Sub SortReceiptsByNameOfContributor()
    ' SortReceiptsByName Macro
    ' Macro recorded 6/18/2012 by Louis F. Sander
    ' Modified on 1/4/15 to work on ID rather than name
    ' On the Finances page, sorts the Receipts entries by the ID of the contributor
    '
        Range("C9").Select
        Range("C8:P65536").Sort Key1:=Range("D8"), Order1:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
            :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal
            
    End Sub
    
    Sub SortReceiptsByDepositDate()
    ' SortReceiptsByDepositDate Macro
    ' Macro recorded 6/18/2012 by Louis F. Sander
    ' Modified on 1/4/15 to accommodate changes in the Finances worksheet
    ' On the Finances page, sorts the Receipts entries by the date they were deposited in the bank
    ' IT REALLY SHOULD FIRST SORT EVERYTHING BY ID
    '
        Range("K9").Select
        Range("C8:P65536").Sort Key1:=Range("K8"), Order1:=xlAscending, Header:= _
            xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
            DataOption1:=xlSortTextAsNumbers
        Range("K9").Select
        Selection.End(xlDown).Select
        
    End Sub
    
    Sub SortReceiptsByDepositDateForBigJackpots()
    ' SortReceiptsByDepositDateForBigJackpots Macro
    ' Macro recorded 6/25/2013 by Rankin Desktop
    ' Modified on 1/4/15 to accommodate changes in the Finances worksheet
    ' On the Finances page, sorts the Big Jackpots Receipts entries by the dates they were deposited in the bank
    ' IT REALLY SHOULD FIRST SORT EVERYTHING BY ID
    '
        Range("P9").Select
        Range("C8:P65536").Sort Key1:=Range("P8"), Order1:=xlAscending, Key2:=Range( _
            "L8"), Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase _
            :=False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal, _
            DataOption2:=xlSortNormal
        Range("P9").Select
        Selection.End(xlDown).Select
    End Sub
    My goal is to get the first two Date sorting buttons to sort by ID within their dates, then to do the same with the third one, which will require that the "sort by check number" feature be removed. I think that all of this stuff is pretty easy, but not for a guy like me whose skills are rusty, and who doesn't really understand the intricacies of the Sorting code.

    After the buttons work as I want them to, I have a few "big picture" questions about optimizing all this stuff.
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  2. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Lou,

    Perhaps if we dissect the following sort command from your code you'll understand what is going on and be able to make the changes yourself. You'll notice that I broke up the lines at more logical places than Excel does it.
    Code:
        Range("P9").Select
        Range("C8:P65536").Sort _ 
                Key1:=Range("P8"), Order1:=xlAscending, _  'First Sort Key and Order 
                Key2:=Range("L8"), Order2:=xlAscending, _  'Second Sort Key and Order (breaks ties on previous level)
                Header:=xlGuess, _                         'Excel will guess if there is a Header/Title row
                OrderCustom:=1, _                          'Custom Sort Order 1 selected
                MatchCase:=False, _                        'Ignore text case in sorting
                Orientation:=xlTopToBottom, _              'Sort Direction could be xlLeftToRight
                DataOption1:=xlSortNormal, _               'Standard Sort for First Key 
                DataOption2:=xlSortNormal                  'Standard Sort for Second Key
    Notes:
    OrderCustom: is non-standard (of course it's custom) usually this is just Order: and it is set to xlAscending or xlDecending.

    CustomOrder:=1 is (Sun,Mon,Tue,Wed,Thu,Fri,Sat)
    CustomSortList.JPG
    You can learn a lot about sorts by recording a macro and making selections from the ribbon and subsequent dialogs then looking at the code they create.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  3. The Following User Says Thank You to RetiredGeek For This Useful Post:

    Lou Sander (2015-01-05)

  4. #3
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I understand it pretty well now, and everything works as I want it to. I've organized the code along your lines, making it much easier to follow.

    The original sorts were made by recording macros, and I didn't do much to change them at that time. I don't know where the several "OrderCustom=1," lines came from, but they are gone now and it doesn't seem to hurt anything. The original code included two "DataOption1:=xlSortTextAsNumbers," statements. I left them in, and they don't seem to do any harm. Here is the revised code for all four macros:

    Code:
    Sub SortReceiptsByDateReceived()
    ' SortReceiptsByDateReceived Macro
    ' Macro recorded 6/27/2013 by Rankin Desktop
    ' Modified on 1/5/15 to accommodate changes in the Finances worksheet and to add a second sort.
    ' On the Finances page, sorts the Receipts entries by the date they were received, then within
    ' each date, it sorts them by ID.
    '
        Range("C8:P65536").Sort _
            Key1:=Range("C8"), Order1:=xlAscending, _
            Key2:=Range("D8"), Order2:=xlAscending, _
            Header:=xlGuess, _
            MatchCase:=False, _
            Orientation:=xlTopToBottom, _
            DataOption1:=xlSortTextAsNumbers, _
            DataOption2:=xlSortNormal
        Range("C9").Select
    End Sub
    
    Sub SortReceiptsByNameOfContributor()
    ' SortReceiptsByName Macro
    '    Macro recorded 6/18/2012 by Louis F. Sander
    '    Modified on 1/5/15 to sort by ID rather than name.
    ' On the Finances page, this sorts the Receipts entries by the ID of the contributor.
    '
        Range("C8:P65536").Sort _
            Key1:=Range("D8"), Order1:=xlAscending, _
            Header:=xlGuess, _
            MatchCase:=False, _
            Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal
        Range("D9").Select
    End Sub
    
    Sub SortReceiptsByDepositDate()
    ' SortReceiptsByDepositDate Macro
    '    Macro recorded 6/18/2012 by Louis F. Sander
    '    Modified on 1/5/15 to accommodate changes in the Finances worksheet and to add a second sort.
    ' On the Finances page, this sorts the Receipts entries by the date they were deposited in the bank;
    ' then within each date, it sorts them by ID.
    '
        Range("C8:P65536").Sort _
            Key1:=Range("K8"), Order1:=xlAscending, _
            Key2:=Range("D8"), Order2:=xlAscending, _
            Header:=xlGuess, _
            MatchCase:=False, _
            Orientation:=xlTopToBottom, _
            DataOption1:=xlSortTextAsNumbers, _
            DataOption2:=xlSortNormal
        Range("K9").Select
        Selection.End(xlDown).Select
        
    End Sub
    
    Sub SortReceiptsByDepositDateForBigJackpots()
    ' SortReceiptsByDepositDateForBigJackpots Macro
    '    Macro recorded 6/25/2013 by Rankin Desktop
    '    Modified on 1/5/15 to accommodate changes in the Finances worksheet and to add a second sort
    ' On the Finances page, this sorts the Big Jackpots Receipts entries by the dates they were deposited
    ' in the bank; then within each date, it sorts them by ID.
    '
        Range("C8:P65536").Sort _
            Key1:=Range("P8"), Order1:=xlAscending, _
            Key2:=Range("D8"), Order2:=xlAscending, _
            Header:=xlGuess, _
            MatchCase:=False, _
            Orientation:=xlTopToBottom, _
            DataOption1:=xlSortNormal, _
            DataOption2:=xlSortNormal
        Range("P9").Select
        Selection.End(xlDown).Select
    End Sub
    The range to be sorted may change from time to time as I add or remove columns, etc. I plan to give it a name and put that name into all the macros, just to make things easier in case of change. Do you agree with that idea?
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

  5. #4
    WS Lounge VIP
    Join Date
    Dec 2009
    Location
    Earth
    Posts
    8,199
    Thanks
    48
    Thanked 987 Times in 917 Posts
    Naming ranges that you use often is always a good idea IMO.

    cheers, Paul

  6. The Following User Says Thank You to Paul T For This Useful Post:

    Lou Sander (2015-01-06)

  7. #5
    5 Star Lounger Lou Sander's Avatar
    Join Date
    Jun 2002
    Location
    Pittsburgh, PA
    Posts
    855
    Thanks
    140
    Thanked 10 Times in 9 Posts
    I went ahead and named the range, then put the name into all the macros that use the range. It works well. A big advantage is that if I add a column inside the range, the sorted area expands to include it.

    Formerly, adding a column caused no end of problems, since I often forgot to modify one or all of the macros. As a result, the rightmost column(s) of the newly-expanded range wouldn't get sorted. Sheesh!
    Lou Sander
    Pittsburgh, Pennsylvania
    USA

Posting Permissions

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