Sorting question

[msnook]

I know that Excel has a built in Sorting function but I was trying to set up a button that I could push and a box would pop up asking which column I wanted to sort by. I then click on that column heading and it would auto sort for me. I can set it up with a button for each column but not one that will do all. For example I have the below data, I press the button choose Name and it would sort all fields alphabetically. If I choose Points it would sort numerically. The charts I am using always stay the same size with the same headers but the data in them changes creating different senarios. If it is just easier to use the built in button than create this new opption I will continue to do that just thought I would ask for some help. Thanks in advance for the input.

Name Points
Bugs Bunny 12
Elmer Fudd 7
Sylvester 13
Jerry 4
Johnny Bravo 6

 

[SkipVought]

Hi,

1. Use your Macro Recorder.

2. Use the ToolBar button for sorting A-Z or Z-A.

post back with your recorded code to get help on the Worksheet_SelectionChange event that will kick off your sort.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[msnook]

here is the toolbar coding
---------------------------------
Range("B3").Select
Range("B2:I16").sort Key1:=Range("B3"), Order1:=xlAscending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
Range("E1").Select
---------------------------------


here is the modifications I am trying to make
---------------------------------
Dim MySearch As Range

MySearch = Application.InputBox("Which column would you like to sort by?")
Range(MySearch).Select
Range("B2:I16").sort Key1:=Range(MySearch), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub
---------------------------------

 

[pinkgecko]

I am not familiar with the range object, so I can't help you with that. But this option, using a string, will achieve the desired effect. You might also want to consider using radio buttons instead of an input box to limit the sortable fields.

Sub Sorter()

Dim MySearch As String

MySearch = Application.InputBox("Which column would you like to sort by?")
MySearch = MySearch & "2:" & MySearch & "16"

    Range(MySearch).Select
      Range("B2:I16").Sort Key1:=Range(MySearch), Order1:=xlDescending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal

End Sub

[blue]When birds fly in the correct formation, they need only exert half the effort. Even in nature, teamwork results in collective laziness.[/blue]

 

[msnook]

When I try the previous code I recieve the error message

Run-time error '1004':
Method 'Range' of object '_Global' failed

If I enter this coding:
---------------------------------------
Dim MySearch As Range

MySearch = Application.InputBox("Which column would you like to sort by?")
Range(MySearch).Select
Range("B2:I16").sort Key1:=Range(MySearch), Order1:=xlDescending, Header:= _
xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

End Sub
---------------------------------------
I get the error message
Run-time error '91':
Object variable or With block bariable not set.

 

[SkipVought]

Why put a button & messagebox between the user and the worksheet?

Use the Worksheet_SelectionChange event so that all the user need do is select a heading for the sort to occur. The ActiveCell is the Key1 range.

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}

 

[msnook]

That is a great idea. what would the coding look like to do this. The example I set was a small range. I am looking at a spreadsheet with 179 rows and 10 columns that I need to sort by various columns depending on which information is most prevelant. That was why I was using the push button to decide which column I would be using.

 

[SkipVought]

In the Sheet Object Code Window, paste...

Sub SortSelection()
    ActiveCell.Sort Key1:=ActiveCell, Order1:=xlDescending, Header:= _
      xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
      DataOption1:=xlSortNormal
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'[A1] assumes that your table heading begin in [A1]
'select a heading and the sort occurs
    If Target.Count > 1 Then Exit Sub
    If Not Intersect(Target, Range([A1], [A1].End(xlToRight))) Is Nothing Then
        SortSelection
    End If
End Sub

Skip,

_{When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large}