Simple Macro (I think)
 

I need a macro that will select a range (let's say A2:D100) and then will pop
open the sort userform box which is built into excel. I would also like the
sort userform when opened to have the "My list has Header Row" choice
selected as a default. From there the user will be able to interact with the
dialog box to select the different ways they may want to sort. Any help?
Thanks

Simple Macro (I think)
 

Jason

the easiest way is to record the actions as you carry them out manually.

You will get:

Range("A2:D100").Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

This can be tidied up a little so that you don't need to select the range:

Range("A2:D100").Sort _
Key1:=Range("A3"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Note that key1 is Range("A3") because it assumes the header row is in row 2.
That may not be what you want, in which case try:

Range("A1:D100").Sort _
Key1:=Range("A2"), _
' : etc
' :

Regards

Trevor

"Jason" wrote in message
...
I need a macro that will select a range (let's say A2:D100) and then will
pop
open the sort userform box which is built into excel. I would also like
the
sort userform when opened to have the "My list has Header Row" choice
selected as a default. From there the user will be able to interact with
the
dialog box to select the different ways they may want to sort. Any help?
Thanks

Simple Macro (I think)
 

Thanks for your help Trevor. However, I do not want to specify what the
criteria is with the macro..... I only want to select the range and then have
the Excel built in sort userform pop up.... and then from there the user will
specify the criteria.... any further ideas?

"Trevor Shuttleworth" wrote:

Jason

the easiest way is to record the actions as you carry them out manually.

You will get:

Range("A2:D100").Select
Selection.Sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlYes, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

This can be tidied up a little so that you don't need to select the range:

Range("A2:D100").Sort _
Key1:=Range("A3"), _
Order1:=xlAscending, _
Header:=xlYes, _
OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal

Note that key1 is Range("A3") because it assumes the header row is in row 2.
That may not be what you want, in which case try:

Range("A1:D100").Sort _
Key1:=Range("A2"), _
' : etc
' :

Regards

Trevor

"Jason" wrote in message
...
I need a macro that will select a range (let's say A2:D100) and then will
pop
open the sort userform box which is built into excel. I would also like
the
sort userform when opened to have the "My list has Header Row" choice
selected as a default. From there the user will be able to interact with
the
dialog box to select the different ways they may want to sort. Any help?
Thanks

Simple Macro (I think)
 

If you really want the form to be presented to the user rather than program
the sort as suggested by Trevor, the following should work. The only quirky
part is that you have to have the SendKeys run before the Execute
instruction goes to bring up the Sort box with a header row assumed:

Sub SortARange()
Dim rngA As Range
Dim cmdBar As CommandBar
Dim cmdBarMenu As CommandBarControl
Dim cmdBarMenuItem As CommandBarControl

Set rngA = Range("A2:D100")
rngA.Select

Set cmdBar = Application.CommandBars("Worksheet Menu Bar")
Set cmdBarMenu = cmdBar.Controls("Data")
Set cmdBarMenuItem = cmdBarMenu.Controls("Sort...")
SendKeys "%R"
cmdBarMenuItem.Execute

End Sub


Steve


"Jason" wrote in message
...
I need a macro that will select a range (let's say A2:D100) and then will
pop
open the sort userform box which is built into excel. I would also like
the
sort userform when opened to have the "My list has Header Row" choice
selected as a default. From there the user will be able to interact with
the
dialog box to select the different ways they may want to sort. Any help?
Thanks

Simple Macro (I think)
 

Steve,

Thanks a lot, this is exactly what I am looking for.


-----Original Message-----
If you really want the form to be presented to the user
rather than program
the sort as suggested by Trevor, the following should
work. The only quirky
part is that you have to have the SendKeys run before
the Execute
instruction goes to bring up the Sort box with a header
row assumed:

Sub SortARange()
Dim rngA As Range
Dim cmdBar As CommandBar
Dim cmdBarMenu As CommandBarControl
Dim cmdBarMenuItem As CommandBarControl

Set rngA = Range("A2:D100")
rngA.Select

Set cmdBar = Application.CommandBars("Worksheet Menu
Bar")
Set cmdBarMenu = cmdBar.Controls("Data")
Set cmdBarMenuItem = cmdBarMenu.Controls("Sort...")
SendKeys "%R"
cmdBarMenuItem.Execute

End Sub


Steve


"Jason" wrote in
message
news:F1498A51-C15C-46F1-BB08-
...
I need a macro that will select a range (let's say
A2:D100) and then will
pop
open the sort userform box which is built into excel.
I would also like
the
sort userform when opened to have the "My list has
Header Row" choice
selected as a default. From there the user will be
able to interact with
the
dialog box to select the different ways they may want
to sort. Any help?
Thanks


.