Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
simple macro | Excel Discussion (Misc queries) | |||
Simple (?) Macro | Excel Discussion (Misc queries) | |||
Simple macro please | New Users to Excel | |||
Simple macro help | Excel Programming | |||
Simple Macro | Excel Programming |