Announcement

Collapse
No announcement yet.

Make a Sort button

Collapse
This topic is closed.
X
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • 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

  • #2
    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

    New users should read the Forum Rules before posting

    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.

    Comment


    • #3
      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

      Code:
      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

      Code:
      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, 18:04. Reason: OOps put row when meant column!
      ________________________________________

      Good Luck!

      Ray

      Comment


      • #4
        Hi pix

        Welcome to ozgrid.com

        See the attached example.
        Attached Files

        Comment


        • #5
          Re: Make a Sort button

          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

          Code:
          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

          Code:
          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

          Comment


          • #6
            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.

            Code:
            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.

            Comment


            • #7
              Re: Make a Sort button

              I think this is an improvement .
              Code:
              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, 17:23.

              Comment


              • #8
                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!!

                Code:
                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, 07:07.

                Comment


                • #9
                  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.
                  Code:
                  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.

                  Comment


                  • #10
                    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.
                    Reafidy

                    Forum Rules | Anatomy Of A Good Thread Title | What Have You Tried So Far? | Are You A Cross Poster?

                    Comment

                    Working...
                    X