Re: Simple Macro (I think)

From: Steve Yandl (syandl_at_comcast.net)
Date: 09/08/04 

Date: Wed, 8 Sep 2004 16:43:44 -0700

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("Work*** Menu Bar")
Set cmdBarMenu = cmdBar.Controls("Data")
Set cmdBarMenuItem = cmdBarMenu.Controls("Sort...")
SendKeys "%R"
cmdBarMenuItem.Execute

End Sub

Steve

"Jason" <Jason@discussions.microsoft.com> wrote in message
news:F1498A51-C15C-46F1-BB08-402DFD36702F@microsoft.com...
>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