Loading
Ozgrid Excel Help & Best Practices Forums

Excel Video Tutorials / Excel Dashboards Reports



Results 1 to 10 of 10

Thread: Make a Sort button

  1. #1
    Join Date
    6th August 2004
    Posts
    1

    Make a Sort button

    Hey all!

    Im fairly new to excel/VBA - and im not to sure how to do the following

    I want a table (of data) to be able to be sorted in asscending or desscending order when the heading of the appropriate coloumn is selected.

    Example;Say i had different size timbre - and the different properties are length,weight,cost,strength. Can i make these titles into 'buttons', so when the user clicks a desired column (say strength) button, the data is sorted into Asscending or Desscending order.

    Hope i made sense, and would appreciate any help

    Cheers pix

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    26th January 2003
    Location
    Derbyshire,UK
    Posts
    18,039
    This will be possible. Run your sort using the Macro Recorder to get the code.

    To make the headers clickable I would place a "hidden" textbox (format to be clear with no lines,sized to cover the cell), then right click it and assign the macro
    Hope that Helps

    Roy

    For free Excel tools & articles visit my web site

    If I have helped you and you feel like putting your hand in your pocket please make a donation to Children in Need

    RoyUK's Web Site

    royUK's Database Form

    Where to paste code from the Forum

    About me.

  3. #3
    Join Date
    6th July 2004
    Location
    UK
    Posts
    505
    Pix, you could make buttons, however this could be a little messy.

    There are two options, 1st is using the following code, first Highlight the top Cell of the column before running

    This is to sort ONLY the selected COLUMN in ascending order

    VB:
    Sub SelAsc() 
         
         
        With Selection.EntireColumn 
            .Sort Key1:=Selection.Offset(1, 0), Order1:=xlAscending, Header:= _ 
            xlGuess, Orientation:=xlTopToBottom 
        End With 
    End Sub 
    
    
    This is to sort ONLY the selected COLUMN in descening order

    VB:
    Sub SelDes() 
         
        With Selection.EntireColumn 
            .Sort Key1:=Selection.Offset(1, 0), Order1:=xlDescending, Header:= _ 
            xlGuess, Orientation:=xlTopToBottom 
        End With 
         
    End Sub 
    
    
    You could then add a short key button to run either!

    The second option would be to utilise similar code employed by Jindon from the following linkhttp://www.ozgrid.com/forum/showthread.php?t=22392

    Good Luck

    Ray
    Last edited by REP013; August 6th, 2004 at 18:04. Reason: OOps put row when meant column!

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    24th January 2003
    Location
    Australia
    Posts
    31,708
    Hi pix

    Welcome to ozgrid.com

    See the attached example.
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

  5. #5
    Join Date
    28th December 2011
    Posts
    2

    Re: Make a Sort button

    Quote Originally Posted by REP013 View Post
    Pix, you could make buttons, however this could be a little messy.

    There are two options, 1st is using the following code, first Highlight the top Cell of the column before running

    This is to sort ONLY the selected COLUMN in ascending order

    VB:
    Sub SelAsc() 
         
         
        With Selection.EntireColumn 
            .Sort Key1:=Selection.Offset(1, 0), Order1:=xlAscending, Header:= _ 
            xlGuess, Orientation:=xlTopToBottom 
        End With 
    End Sub 
    
    
    This is to sort ONLY the selected COLUMN in descening order

    VB:
    Sub SelDes() 
         
        With Selection.EntireColumn 
            .Sort Key1:=Selection.Offset(1, 0), Order1:=xlDescending, Header:= _ 
            xlGuess, Orientation:=xlTopToBottom 
        End With 
         
    End Sub 
    
    
    You could then add a short key button to run either!

    The second option would be to utilise similar code employed by Jindon from the following linkhttp://www.ozgrid.com/forum/showthread.php?t=22392

    Good Luck

    Ray

    Hi,

    The code listed here works perfectly but I wish to have a row offset, i.e it only sorts from row 3 downwards. Can I amend this easily to acheive this.

    Many Thanks

    Excel Video Tutorials / Excel Dashboards Reports


  6. #6
    Join Date
    23rd April 2007
    Posts
    3,434

    Re: Make a Sort button

    Put this in your sheet's code module.
    Double click on a cell, the data range will be sorted on that column.
    The sort will "alternate" between ascending and descending, double click on the cell again if you want it the other way.

    VB:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
        Dim dataRange As Range, tColumn As Range 
        Dim sortType As Long 
        Dim fStr As String 
        fStr = "000000000000000.00000000000000" 
         
        Set dataRange = Target.CurrentRegion: Rem adjust? 
         
        Set tColumn = Application.Intersect(dataRange, Target.EntireColumn) 
         
        sortType = IIf(Format(CStr(tColumn.Cells(3, 1).Value), fStr) <= Format(CStr(tColumn.Cells(2, 1).Value), fStr), xlAscending, xlDescending) 
         
        dataRange.Sort key1:=tColumn.Cells(1, 1), order1:=sortType, Header:=xlYes 
        Cancel = True 
    End Sub 
    
    
    The determination of the dataRange can be changed.

    Excel Video Tutorials / Excel Dashboards Reports


  7. #7
    Join Date
    23rd April 2007
    Posts
    3,434

    Re: Make a Sort button

    I think this is an improvement .
    VB:
    Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean) 
        Dim RangeToSort As Range, TargetHeaderCell As Range 
        Static sortType As Long 
         
        sortType = 3 - Application.Max(sortType, xlAscending) 
         
        Set RangeToSort = Target.CurrentRegion: Rem adjust? 
        Set TargetHeaderCell = Application.Intersect(RangeToSort.Rows(1), Target.EntireColumn) 
         
        If Not TargetHeaderCell Is Nothing Then 
            RangeToSort.Sort key1:=TargetHeaderCell, order1:=sortType, Header:=xlGuess 
            Cancel = True 
        End If 
    End Sub 
    
    
    Last edited by mikerickson; December 28th, 2011 at 16:23.

    Excel Video Tutorials / Excel Dashboards Reports


  8. #8
    Join Date
    28th December 2011
    Posts
    2

    Re: Make a Sort button

    Hi Mike,

    Thanks for your help but neither of these produce the outcome I need. Both versions include the header rows in the sort and although
    running it from a double click is nice my intention is no run a seperate ascending and descending of buttons...

    I have something running as below but it only sorts the column i've selected so the data gets screwed up. Any more help appreciated!!

    VB:
    Private Sub btnSortZ_Click() 
        SortedCol = ActiveCell.Column 
        ActiveSheet.Range("A3").Select 
        NumofRows = 0 
         
        Do While ActiveCell <> "" 
            NumofRows = NumofRows + 1 
            ActiveCell.Offset(1, 0).Select 
        Loop 
         
         
        With ActiveSheet 
            .Range(.Rows(3), .Rows(NumofRows + 2)).Sort Key1:=.Range(SortedCol & 3), _ 
            Order1:=xlAscending, Header:=xlNo 
             
        End With 
         
    End Sub 
    
    
    Last edited by Reafidy; December 29th, 2011 at 06:07.

    Excel Video Tutorials / Excel Dashboards Reports


  9. #9
    Join Date
    23rd April 2007
    Posts
    3,434

    Re: Make a Sort button

    What is the range of data that you want sorted? Is it fixed or have a variable number of rows?

    Your headers are all in the top row? (no merged cells) If that's the case, this should fix that problem.
    VB:
    RangeToSort.Sort key1:=TargetHeaderCell, order:=sortType, Header:=xlYes 
    
    
    If you could attach a small sample workbook, with everything in the right places, details like this could be firmed up.

    Excel Video Tutorials / Excel Dashboards Reports


  10. #10
    Join Date
    8th December 2006
    Location
    New Zealand
    Posts
    3,914

    Re: Make a Sort button

    oscar42,


    As per the forum rules, start your own thread if you have a question. Please make sure you read the rules as you have also not used code tags for posting your code.

Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Make Button Caption Diagonal
    By mustang_07 in forum EXCEL HELP
    Replies: 1
    Last Post: January 5th, 2008, 21:08
  2. Make Button Flash
    By Trebor76 in forum EXCEL HELP
    Replies: 3
    Last Post: December 1st, 2006, 13:53
  3. Clicking Command Button to make label visible
    By bearcub in forum Excel and/or Access Help
    Replies: 2
    Last Post: May 18th, 2006, 00:04
  4. Make Button on tool Bar
    By ParTeeGolfer in forum EXCEL HELP
    Replies: 3
    Last Post: March 9th, 2006, 09:43
  5. Email : how do i make a button to e-mail an excel spreadshee
    By ries in forum Excel and/or Email Help
    Replies: 5
    Last Post: January 23rd, 2004, 12:00

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
  •  
porno