Sort command
 

Hey guys,

I have a name in each cell from A6 through U6. The
objective is to be able to sort by each of these names. I
will use a form that will contain 3 objects: A DROP DOWN
BOX, OK button, and CANCEL button. I need for each name
in range A6:U6 to show up in the drop down box. I will
select a name from the drop down box and click the OK
button. When I click the OK button, I need it to sort
data in range A7:U55 by the name I selected from the drop
down box.

Can anyone tell me the code?

Thanx


Todd Huttenstine

Sort command
 

code is untested and may contain typos:

Private Sub cmdOK_click() '< == OK
Dim rng As Range, rng1 As Range
Dim res As Variant
Dim rng2 As Range
With Worksheets("sheet1")
Set rng = .Range("A6:U55")
Set rng1 = .Range("A6:U6")
End With
res = Application.Match(combobox1.Value, rng1, 0)
If Not IsError(res) Then
Set rng2 = rng1(1, res)
rng.Sort Key1:=rng2, Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End if
Unload Me
End Sub


Private Sub Userform_Initialize
Dim rng As Range
With Worksheets("sheet1")
Set rng = .Range("A6:U6")
End With
for each cell in rng
Combobox1.AddItem cell.Value
Next
End With

Private Sub cmdCancel_Click()
Unload Me
End Sub

--
Regards,
Tom Ogilvy



Todd Huttenstine wrote in message
...
Hey guys,

I have a name in each cell from A6 through U6. The
objective is to be able to sort by each of these names. I
will use a form that will contain 3 objects: A DROP DOWN
BOX, OK button, and CANCEL button. I need for each name
in range A6:U6 to show up in the drop down box. I will
select a name from the drop down box and click the OK
button. When I click the OK button, I need it to sort
data in range A7:U55 by the name I selected from the drop
down box.

Can anyone tell me the code?

Thanx


Todd Huttenstine

Sort command
 

Add this code to your userform initialize event complete it for your
purpose:
ComboBox1.AddItem ""
ComboBox1.AddItem Cells(6, 1) ' equals A6
ComboBox1.AddItem Cells(6, 2) ' equals B6
ComboBox1.AddItem Cells(6, 3) 'C6
ComboBox1.AddItem Cells(6, 4) 'D6
ComboBox1.AddItem Cells(6, 5) 'E6
ComboBox1.AddItem Cells(6, 6) 'F6
this will fill the contents of the cells into your combobox or dropdown.
Now add this code to your "Okay" command button on your userform. Again
complete as you need. You might want to play around with this code and try
using Select Case instead of all my IF's, but user preference.
'Checks for which cell combobox is equal to
If ComboBox1.Value = Cells(6,1) Then
Range("A7:U55").Select 'selects the range to sort
Selection.Sort Key1:=Range("A6"), ' sets the order to sort
Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ElseIf ComboBox1.Value = Cells(6,2) Then
Range("A7:U55").Select
Selection.Sort Key1:=Range("B6"), Order1:=xlAscending, Header:=xlGuess,
_
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
ElseIf ComboBox1.Value = Cells(6,3) Then
Range("A7:U55").Select
Selection.Sort Key1:=Range("C6"), Order1:=xlAscending,
Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom

End If
This should get you started.
HTH
"Todd Huttenstine" wrote in message
...
Hey guys,

I have a name in each cell from A6 through U6. The
objective is to be able to sort by each of these names. I
will use a form that will contain 3 objects: A DROP DOWN
BOX, OK button, and CANCEL button. I need for each name
in range A6:U6 to show up in the drop down box. I will
select a name from the drop down box and click the OK
button. When I click the OK button, I need it to sort
data in range A7:U55 by the name I selected from the drop
down box.

Can anyone tell me the code?

Thanx


Todd Huttenstine

Sort command
 

much smaller and quicker than mine. I'll use it from now on.

"Tom Ogilvy" wrote in message
...
code is untested and may contain typos:

Private Sub cmdOK_click() '< == OK
Dim rng As Range, rng1 As Range
Dim res As Variant
Dim rng2 As Range
With Worksheets("sheet1")
Set rng = .Range("A6:U55")
Set rng1 = .Range("A6:U6")
End With
res = Application.Match(combobox1.Value, rng1, 0)
If Not IsError(res) Then
Set rng2 = rng1(1, res)
rng.Sort Key1:=rng2, Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, Orientation:=xlTopToBottom
End if
Unload Me
End Sub


Private Sub Userform_Initialize
Dim rng As Range
With Worksheets("sheet1")
Set rng = .Range("A6:U6")
End With
for each cell in rng
Combobox1.AddItem cell.Value
Next
End With

Private Sub cmdCancel_Click()
Unload Me
End Sub

--
Regards,
Tom Ogilvy



Todd Huttenstine wrote in message
...
Hey guys,

I have a name in each cell from A6 through U6. The
objective is to be able to sort by each of these names. I
will use a form that will contain 3 objects: A DROP DOWN
BOX, OK button, and CANCEL button. I need for each name
in range A6:U6 to show up in the drop down box. I will
select a name from the drop down box and click the OK
button. When I click the OK button, I need it to sort
data in range A7:U55 by the name I selected from the drop
down box.

Can anyone tell me the code?

Thanx


Todd Huttenstine