Macro that allows user to set a sort range and then executes the sort

[TNCRAIG]

I'm attempting to create a macro that will allow a user to set the sort range, field sort parameters, and then perform the sort. I'm able to have the macro bring up an excel input box (I copied an example from a John Walkenbach book) and have the user select the sort range but after that I'm lost. I DON'T KNOW VBA as the example below embarrassingly shows and any help would be greatly appreciated. Here is what the non-functioning code currently looks like:

Sub SortCash()
'
' SortCash Macro
' Sort Cash Rows
'
' Keyboard Shortcut: Ctrl+s
'
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=ActiveCell.Offset(0, 0), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=ActiveCell.Offset(0 _
, 3), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
ActiveSheet.Sort.SortFields.Add Key:=ActiveCell.Offset(0 _
, 1), SortOn:=xlSortOnValues, Order:=xlAscending, _
DataOption:=xlSortNormal
With ActiveSheet.Sort
Dim UserRange As Range
DefaultRange = Selection.Address
Set UserRange = Application.InputBox _
(Prompt:="Select Cells to Sort:", _
Title:="Range to Sort", _
Default:=DefaultRange, _
Type:=8)
UserRange.Select
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
End Sub

 

[JLGWhiz]

Would this not work?

Sub SortCash2()
Dim UserRange As Range
Set UserRange = Application.InputBox(Prompt:="Select Cells to Sort:", Title:="Range to Sort", _
 Default:=DefaultRange, Type:=8)
 MsgBox UserRange.Address
UserRange.Sort Key1:=UserRange.Cells(1, 1), Order1:=xlAscending, Key2:=UserRange.Cells(1, 4), Order2:=xlAscending, _
Key3:=UserRange.Cells(1, 2), Order3:=xlAscending, Header:=xlYes
End Sub

You might have a problem with using ActiveCell as your Key range reference. If the user is going to select a range with the inputbox, the ActiveCell could be anywhere but inside the selected range. In that case, VBA would throw an error. Even using the method you originally posted, the same problem occurs. This code uses the UserRange cells as a range reference to define the Key ranges. Curiosity begs to question if the Key range sequence of 1, 4, 2 will apply in all cases? Will the user be able to change these references? No reply is necessary, just wondering.

 

[TNCRAIG]

Would this not work?

Sub SortCash2()
Dim UserRange As Range
Set UserRange = Application.InputBox(Prompt:="Select Cells to Sort:", Title:="Range to Sort", _
 Default:=DefaultRange, Type:=8)
 MsgBox UserRange.Address
UserRange.Sort Key1:=UserRange.Cells(1, 1), Order1:=xlAscending, Key2:=UserRange.Cells(1, 4), Order2:=xlAscending, _
Key3:=UserRange.Cells(1, 2), Order3:=xlAscending, Header:=xlYes
End Sub

You might have a problem with using ActiveCell as your Key range reference. If the user is going to select a range with the inputbox, the ActiveCell could be anywhere but inside the selected range. In that case, VBA would throw an error. Even using the method you originally posted, the same problem occurs. This code uses the UserRange cells as a range reference to define the Key ranges. Curiosity begs to question if the Key range sequence of 1, 4, 2 will apply in all cases? Will the user be able to change these references? No reply is necessary, just wondering.

"JLGWhiz, it does work. Thank you for the quick response. The spreadsheet will have two different Key range sequences. I thought I would create a second macro and just change the sort key references."

 

[JLGWhiz]

Glad you could use it.
Regards, JLG