Make a Sort button

[pix_jnr]

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

[royUK]

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

[REP013]

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

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

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

[Dave Hawley]

Hi pix

Welcome to ozgrid.com

See the attached example.

[oscar42]

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

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

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

[mikerickson]

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.

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.

[mikerickson]

I think this is an improvement .

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 

[oscar42]

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!!

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 

[mikerickson]

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.

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.

[Reafidy]

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.