Tom,

Followed the code and checked for errors, however I'm getting a run-time
error at the second last line of the code

res = Application.Match(scol, .rng.Rows(1).Cells, 0)

"object doesn't support this property or method"

I can't figure out why...



"Tom Ogilvy" wrote in message
...
That is why I said Hide rather than unload

Assume in the Userform

Private Sub CmdOK_Click()
me.hide
End Sub


Then in the procedure that called the userform


Sub MySort()
Userform1.Show
' get the column
for each ctrl in Userform1.Frame1.Controls
if typeof ctrl is MSForms.OptionButton then
if ctrl.Value = True then
scol = ctrl.Caption
exit for
end if
end if
Next
if scol = "" then
' if no option button was set sort on column header "ABC"
scol = "ABC"
End if
lOrder = xlAscending
if userform1.Frame2.OptionButton4.Value = true then
lOrder = xlDescending
End if

' now unload the userform

Unload Userform1

' now sort the data

With Worksheets("Sheet1")
set rng = .Range("A1").CurrentRegion
res = Application.Match(sCol,.rng.Rows(1).Cells,0)
rng.Sort Key:=.Cells(1,res), Order1:=lOrder
End With
End sub

Code is pseudo code - untested and may contain typos, but should give you
a sense of how to do this.
--
Regards,
Tom Ogilvy



"Gazza" wrote in message
...
Tom,

thanks for that, what I am a little confused about is how do I pass the
results of each of the 2 options to the sort routine.

regards

Gaz

"Tom Ogilvy" wrote in message
...
Just some added information. Assuming the userform is only to gather
the
sort specification and then will be dropped - then
If you do put it in a general module, then the code would need to have
access to the controls on the userform, so you would hide the form
rather
than unload it.

You can have the code that does the sort unload it once it gets the
information it needs.

--
Regards,
Tom Ogilvy



"Gazza" <mallin"nospam" wrote:

I have a user form which I want to use to sort data on a worksheet
(having
defined a name for the data rnage). There are 2 frames on the user
form. The
first frame has 3 options buttons which I want the user to decide from
the 3
columns which to use as the sort (using the relevent row header). The
second
frame has 2 option buttons which the user can use to determine whether
the
sort is ascending or descending.

will the code that does all this need to be written in a general module
or
should it be written as an event on say a "run button" on the form

Thanks

Gaz