Results 1 to 8 of 8
  1. #1
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts

    Sort & Resort Button

    I'm using a button to sort data by column Q, I have multiple sheets and sometimes the important data to sort to the top of the sheet is a negative number and sometimes its a positive number. i'd like to have the button's macro work so if i clicked it once and i needed to re-sort data in the opposite direction, i could just click the button again to re-sort. how do write the macro to do this double duty, when i assign it to the button?

  2. #2
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    Unome,

    Here is some sample data with a button that has a caption to sort either ascending or descending. Clicking the button will sort the the data in the direction specified and the button's caption will change to indicate that clicking it again will sort it in the other direction.

    Click the Sort Descending button:
    Unome1.png

    Click the Sort Ascending button:
    Unome2.png

    In the Worksheet module:
    Code:
    Private Sub CommandButton1_Click()
    With CommandButton1
    '---------------------------------
    'SORT ASCENDING
        If .Caption = "Sort Ascending" Then
            .Caption = "Sort Descending"
            SortAscending
    '---------------------------------
    'SORT DESCENDING
        Else:
            .Caption = "Sort Ascending"
            SortDescending
        End If
    End With
    End Sub
    In a Standard module:
    Code:
    Sub SortAscending()
        Dim rng As Range, keyrng As Range
        Set rng = Range("Q1:S45") 'CHANGE TO RANGE OF ENTIRE ARRAY
        Set keyrng = Range("Q1:Q45")  'CHANGE TO RANGE OF SORT KEY
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveSheet.Sort.SortFields.Add Key:=keyrng, Order:=xlAscending
        With ActiveSheet.Sort
            .SetRange rng
            .Header = xlYes
            .Apply
        End With
    End Sub
    
    
    
    Sub SortDescending()
        Dim rng As Range, keyrng As Range
        Set rng = Range("Q1:S45") 'CHANGE TO RANGE OF ENTIRE ARRAY
        Set keyrng = Range("Q1:Q45")  'CHANGE TO RANGE OF SORT KEY
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveSheet.Sort.SortFields.Add Key:=keyrng, Order:=xlDescending
        With ActiveSheet.Sort
            .SetRange rng
            .Header = xlYes
            .Apply
        End With
    End Sub
    This could be easily set up to detect your last row but I am not sure if you have data located below the data to be sorted.

    HTH,
    Maud
    Attached Files Attached Files

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

    Unome (2015-03-01)

  4. #3
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,434
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Maud & Unome,

    Nice code Maud! If I may a slight modification which I think will make the code easier to maintain.

    Button Code (Sheet1 module):
    Code:
    Option Explicit
    
    Private Sub CommandButton1_Click()
    
       With CommandButton1
    
       'SORT ASCENDING
         If .Caption = "Sort Ascending" Then
           .Caption = "Sort Descending"
           ToggleSort xlAscending
           
       'SORT DESCENDING
         Else:
           .Caption = "Sort Ascending"
           ToggleSort xlDescending
         End If
         
       End With
    
    End Sub   'CommandButton1_Click()
    Sort Code (SORTING module):
    Code:
    Option Explicit
    
    Sub ToggleSort(iOrder As Integer)
        Dim rng As Range, keyrng As Range
        Set rng = Range("Q1:S45") 'CHANGE TO RANGE OF ENTIRE ARRAY
        Set keyrng = Range("Q1:Q45")  'CHANGE TO RANGE OF SORT KEY
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveSheet.Sort.SortFields.Add Key:=keyrng, Order:=iOrder
        With ActiveSheet.Sort
            .SetRange rng
            .Header = xlYes
            .Apply
        End With
    End Sub
    Now you only have to maintain your ranges in a single Sub routine and there is less code overall.

    HTH
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    Unome (2015-03-01)

  6. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,637
    Thanks
    115
    Thanked 650 Times in 592 Posts
    RG,
    Nice Tweak!!!

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    Unome (2015-03-01)

  8. #5
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts
    When i set this up in my spreadsheet i get an error at the .Apply line.
    Any ideas what i may have done wrong?
    Code:
    Sub ToggleSort(iOrder As Integer)
        Dim rng As Range, keyrng As Range
        Set rng = Range("a7:Q999") 'CHANGE TO RANGE OF ENTIRE ARRAY
        Set keyrng = Range("Q7:Q999")  'CHANGE TO RANGE OF SORT KEY
        ActiveWorkbook.Worksheets("Sheet1").Sort.SortFields.Clear
        ActiveSheet.Sort.SortFields.Add Key:=keyrng, Order:=iOrder
        With ActiveSheet.Sort
            .SetRange rng
            .Header = xlYes
       End With
    End Sub
    Last edited by RetiredGeek; 2015-03-01 at 14:09. Reason: Added Code Tags

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

    This line: Set rng = Range("a7:Q999") 'CHANGE TO RANGE OF ENTIRE ARRAY

    Should read: Set rng = Range("Q7:S999") 'CHANGE TO RANGE OF ENTIRE ARRAY

    At least from the workbook that Maud Posted.

    HTH
    Last edited by RetiredGeek; 2015-03-01 at 14:22.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    Unome (2015-03-01)

  11. #7
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts
    ok, i made the change and got an error: "the sort reference is not valid. make sure that its withing the data you want to sort, and the first sort by box isnt the same or blank..."so i changed the rng code to:

    Option Explicit
    Sub ToggleSort(iOrder As Integer)
    Dim rng As Range, keyrng As Range
    Set rng = Range("Q7:S999") 'CHANGE TO RANGE OF ENTIRE ARRAY
    Set keyrng = Range("Q7:Q999") 'CHANGE TO RANGE OF SORT KEY
    ActiveWorkbook.Worksheets("Sheet1").Sort.SortField s.Clear
    ActiveSheet.Sort.SortFields.Add Key:=keyrng, Order:=iOrder
    With ActiveSheet.Sort
    .SetRange rng
    .Header = xlYes
    .Apply
    End With
    End Sub

    and i get the same error, The range of my data is from row 7 to row 999 (rows 1-6 is header infomation), col A through Q, and i want to sort by column Q (starting at Q7) and Q7 isnt blank, However Q6 is blank (if that matters). Sorry to keep posting on this issue, i appreciate your help!

  12. #8
    Star Lounger
    Join Date
    Mar 2006
    Posts
    76
    Thanks
    6
    Thanked 0 Times in 0 Posts
    Guy's THANKS so much... i figured out what i was doing wrong...
    here is how i got it to work as you designed for Sheet 1, i was on sheet 2
    Option Explicit

    Sub ToggleSort(iOrder As Integer)
    Dim rng As Range, keyrng As Range
    Set rng = Range("a6:S999") 'CHANGE TO RANGE OF ENTIRE ARRAY
    Set keyrng = Range("Q6:Q999") 'CHANGE TO RANGE OF SORT KEY
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Clear
    ActiveWorkbook.ActiveSheet.Sort.SortFields.Add Key:=keyrng, Order:=iOrder
    With ActiveSheet.Sort
    .SetRange rng
    .Header = xlYes
    .Apply
    End With
    End Sub

Posting Permissions

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