Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
User form to sort data
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
User form to sort data
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. "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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
User form to sort data
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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
User form to sort data
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
User form to sort data
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 |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
User form to sort data
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 |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
User form to sort data
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 -- Regards, Tom Ogilvy "Gazza" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Form to sort data | Excel Discussion (Misc queries) | |||
displaying the user form data | Excel Programming | |||
user form - data range | Excel Programming | |||
Data from user form to certain cell | Excel Programming | |||
Reading Data from a User Form. | Excel Programming |