Simple Macro (I think)

J

Jason

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
 
T

Trevor Shuttleworth

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
 
J

Jason

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?
 
S

Steve Yandl

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
 
Top