User form to sort data

[Gazza]

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

 

[Guest]

What I generally try to do is use the controls on my user form to call
procedures from the main code module. That means that the main code module
contains the heart of the program while the user forms provide an interface
for users. What it boils down to is how you write your code and how it best
suits your purpose. It is the end quality product that counts.

 

[Guest]

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.

 

[Gazza]

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]

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.

 

[Gazza]

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]

Now that I have seen the original it appears my memory was not that
accurate. Here is a correction, but again, it is untested - you may have to
make some adjustments.

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,.Rows(1).Cells,0)
rng.Sort Key:=.Cells(1,res), Order1:=lOrder
End With
End sub