+ Reply to Thread
Results 1 to 8 of 8

Sorting with Buttons

Hybrid View

jeffcp66 Sorting with Buttons 09-25-2019, 11:40 AM
maras_mak Re: Sorting with Buttons 09-25-2019, 01:31 PM
Leith Ross Re: Sorting with Buttons 09-25-2019, 01:52 PM
jeffcp66 Re: Sorting with Buttons 09-25-2019, 02:22 PM
Leith Ross Re: Sorting with Buttons 09-25-2019, 03:15 PM
jeffcp66 Re: Sorting with Buttons 09-25-2019, 03:48 PM
Leith Ross Re: Sorting with Buttons 09-25-2019, 08:59 PM
jeffcp66 Re: Sorting with Buttons 09-26-2019, 05:10 PM
  1. #1
    Registered User
    Join Date
    03-22-2019
    Location
    Portland, Oregon
    MS-Off Ver
    Mac 2016
    Posts
    58

    Sorting with Buttons

    I'm having an issue with the VBA Sort function when using a button to activate the Sort. The code seems to work one time when I first try it, but then does not work after that.

    Columns A-G need to be sorted based on data in column D for "Symbol" button, or column E for "Entry Date" button. I am using cell names to indicate location as this "Jumper" section will move down the sheet as more records are added above. (and columns B & C are blank and need to remain blank)

    Here is the code that is not working, and I have attached a stripped down version of the document. The macros in question are entitled "Sort_Jumper_Symbol" and "Sort_Jumper_Entry".

    What am I doing wrong?

    Sub Sort_Jumper_Symbol()
    
    Dim symb As Range
    Dim entr As Range
    Dim Jump As Range
    Dim srtX As Integer
    
    Set Jump = ActiveSheet.Buttons(Application.Caller).TopLeftCell
    Set symb = Jump.Offset(1, 0)
    Set entr = Jump.Offset(1, 1)
    srtX = Range(Jump.Offset(1, -3), Jump.Offset(1, -3).End(xlDown)).Count
                    'ActiveCell.Offset(0, -10).Select
    'Range(ActiveSheet.Buttons(Application.Caller).TopLeftCell.Offset(1, -1), ActiveSheet.Buttons(Application.Caller).TopLeftCell.Offset(srt, 1)).Sort [G1123], xlAscending, Header:=xlYes
            
    With ActiveSheet.Sort
         .SortFields.Add Key:=Range("JumpSym"), Order:=xlAscending
        .SortFields.Add Key:=Range("JumpEntry"), Order:=xlAscending
         .SetRange Range(Jump.Offset(1, -3), _
            Jump.Offset(srtX + 1, 3))
         .Header = xlNo
         .Apply
    End With
    Jump.Offset(1, 0).Select
       
    End Sub
    Attached Files Attached Files

  2. #2
    Valued Forum Contributor
    Join Date
    07-14-2017
    Location
    Poland
    MS-Off Ver
    Office 2010
    Posts
    536

    Re: Sorting with Buttons

    I don't know if I understood you correctly.
    In my solution, all cells in the B: G range must be unmerged.
    I used helper column 'J' for sorting.
    Sorting for "Symbol".
    Sub Sort_Symbol()
        Dim lr As Integer
        
        With Application
            .EnableEvents = False
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With
        With Sheets("Journal")
            lr = .Cells(Rows.Count, "B").End(xlUp).Row + 5
            .Range("J6:J" & lr).Formula = "=INDEX(B:B,AGGREGATE(15,6,ROW($B$1:$B$600)/" _
                    & "($B$1:$B$600=""Symbol""),COUNTIF($B$5:B6,""Symbol""))+1)"
            With Sheets("Journal").Range("B6:J" & lr)
                       .Sort Key1:=.Cells(1, 9), order1:=xlAscending, _
                               Header:=xlGuess
            End With
            .Columns("J").ClearContents
        End With
        With Application
            .EnableEvents = True
            .Calculation = xlCalculationAutomatic
        End With
    End Sub
    Sorting for "Entry Date".
    Sub Sort_Entry_Date()
        Dim lr As Integer
        
        With Application
            .EnableEvents = False
            .Calculation = xlCalculationManual
            .ScreenUpdating = False
        End With
        With Sheets("Journal")
            lr = .Cells(Rows.Count, "B").End(xlUp).Row + 5
            .Range("J6:J" & lr).Formula = "=INDEX(C:C,AGGREGATE(15,6,ROW($B$1:$B$600)/" _
                    & "($B$1:$B$600=""Symbol""),COUNTIF($B$5:B6,""Symbol""))+1)"
            With Sheets("Journal").Range("B6:J" & lr)
                       .Sort Key1:=.Cells(1, 9), order1:=xlAscending, _
                               Header:=xlGuess
            End With
            .Columns("J").ClearContents
        End With
        With Application
            .EnableEvents = True
            .Calculation = xlCalculationAutomatic
        End With
    End Sub
    Best Regards,
    Maras.

  3. #3
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Sorting with Buttons

    Hello jeffcp66,

    I made a few changes to your macro and this version worked for me.

    Sub Sort_Jumper_Symbol()
    
        Dim symb    As Range
        Dim entr    As Range
        Dim Jump    As Range
        Dim srtRng  As Range
    
            Set Jump = ActiveSheet.Buttons(Application.Caller).TopLeftCell
            Set symb = Jump.Offset(1, 0)
            Set entr = Jump.Offset(1, 1)
            
            Set srtRng = Jump.Offset(2, 0).CurrentRegion
            Set srtRng = Intersect(srtRng, srtRng.Offset(1, 0))
            
            With ActiveSheet.Sort
                .SortFields.Clear
                .SortFields.Add Key:=symb, SortOn:=xlSortOnValues, Order:=xlAscending
                .SortFields.Add Key:=entr, SortOn:=xlSortOnValues, Order:=xlAscending
            
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SetRange srtRng
                .Apply
            End With
    
            Jump.Offset(1, 0).Select
    
    End Sub
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)

  4. #4
    Registered User
    Join Date
    03-22-2019
    Location
    Portland, Oregon
    MS-Off Ver
    Mac 2016
    Posts
    58

    Re: Sorting with Buttons

    Thanks Leith! That is sorting correctly, but it is only sorting columns D-G. I need A-G sorted, but can't figure how to adjust your code to do that.

  5. #5
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Sorting with Buttons

    Hello jeffcp66,

    Here is the amended macro code to include columns "A:G".

    Sub Sort_Jumper_Symbol()
    
        Dim symb    As Range
        Dim entr    As Range
        Dim Jump    As Range
        Dim srtRng  As Range
        
            Set Jump = ActiveSheet.Buttons(Application.Caller).TopLeftCell
            Set symb = Jump.Offset(1, 0)
            Set entr = Jump.Offset(1, 1)
            
            Set srtRng = Jump.Offset(2, 0).CurrentRegion.Offset(0, -3)
            Set srtRng = Intersect(srtRng, srtRng.Offset(2, 0))
            
            With ActiveSheet.Sort
                .SortFields.Clear
                .SortFields.Add Key:=symb, SortOn:=xlSortOnValues, Order:=xlAscending
                .SortFields.Add Key:=entr, SortOn:=xlSortOnValues, Order:=xlAscending
            
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SetRange srtRng
                .Apply
            End With
    
            Jump.Offset(1, 0).Select
    
    End Sub

  6. #6
    Registered User
    Join Date
    03-22-2019
    Location
    Portland, Oregon
    MS-Off Ver
    Mac 2016
    Posts
    58

    Re: Sorting with Buttons

    That's not working, I get "Run-time error '1004': The sort reference is not valid. Make sure that it's within the data you want to sort, and the first Sort By box isn't the same or blank." When I click "Debug" it highlights the code line ".Apply"

  7. #7
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: Sorting with Buttons

    Hello jeffcp66,

    I was out most of the day on other jobs. Here is the amended macro code...

    Sub Sort_Jumper_Symbol()
    
        Dim symb    As Range
        Dim entr    As Range
        Dim Jump    As Range
        Dim srtRng  As Range
        
            Set Jump = ActiveSheet.Buttons(Application.Caller).TopLeftCell
            Set symb = Jump.Offset(1, 0)
            Set entr = Jump.Offset(1, 1)
            
            Set srtRng = Jump.CurrentRegion
            Set srtRng = srtRng.Offset(1, -3).Resize(ColumnSize:=srtRng.Columns.Count + 3)
            
            With ActiveSheet.Sort
                .SortFields.Clear
                .SortFields.Add Key:=symb, SortOn:=xlSortOnValues, Order:=xlAscending
                .SortFields.Add Key:=entr, SortOn:=xlSortOnValues, Order:=xlAscending
            
                .Header = xlYes
                .MatchCase = False
                .Orientation = xlTopToBottom
                .SetRange srtRng
                .Apply
            End With
    
            symb.Select
    
    End Sub

  8. #8
    Registered User
    Join Date
    03-22-2019
    Location
    Portland, Oregon
    MS-Off Ver
    Mac 2016
    Posts
    58

    Re: Sorting with Buttons

    Thanks, it works now!

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macros with buttons, spin buttons, scroll buttons, etc.
    By qqbbppdd in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 12-12-2013, 09:34 AM
  2. How to set buttons for sorting if there are already fileters?
    By toplisek in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 10-08-2013, 07:13 AM
  3. enable certain combo buttons based on user selected option buttons
    By welchs101 in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-20-2013, 06:43 AM
  4. How to insert and user of Buttons, Radio buttons, check boxes in excel sheet.
    By krishco in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 06-03-2013, 02:52 AM
  5. How to insert Buttons, radio buttons and check boxes in Excel
    By krishco in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-01-2013, 08:13 AM
  6. How can I insert multiple sets of option buttons (radio buttons) in Excel 2010?
    By mickwooduclan in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 04-25-2013, 10:01 AM
  7. [SOLVED] Option Buttons-putting questionaire using options buttons
    By Ashman in forum Excel General
    Replies: 2
    Last Post: 07-10-2005, 04:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1