I have two columns in a spreadsheet labeled Project No. and Project Name. As
users continually add entries to those columns, I want to create a button
called "Sort by Project Number" at the top that toggles between ascending and
descending order (in other words, if the Project No. column is already sorted
in ascending order, then clicking the button sorts it in descending order,
and vice versa). If it's not sorted, then sort it in ascending order.

I know the sorting buttons exist on the toolbar, but these users need
something simple and obvious, and doing it with one button would help.
Thanks!

Steve C

Sub ABC()
Dim res As Variant
Dim rng As Range
Dim v As Long
Dim ord As Long
res = Application.Match("Project No.", Range("A1:IV1"), 0)
if iserror(res) then
msgbox "Column with header Project No. not found"
exit sub
end if
Set rng = Range(Cells(2, res), Cells(Rows.Count, res).End(xlUp))
v = Application.Evaluate("Sumproduct(--(" & rng.Offset(1, 0) _
.Resize(rng.Count - 1, 1).Address(0, 0) & "=" & _
rng.Resize(rng.Count - 1, 1).Address(0, 0) & "))")

ord = xlAscending
If v = rng.Count - 1 Then
ord = xlDescending
End If
rng.Resize(, 2).Sort _
Key1:=rng(1), _
Order1:=ord, _
Header:=xlNo
End Sub

worked for me. It assumes Project Name is to the left of Project No.

--
Regards,
Tom Ogilvy


"Steve C" wrote:

I have two columns in a spreadsheet labeled Project No. and Project Name. As
users continually add entries to those columns, I want to create a button
called "Sort by Project Number" at the top that toggles between ascending and
descending order (in other words, if the Project No. column is already sorted
in ascending order, then clicking the button sorts it in descending order,
and vice versa). If it's not sorted, then sort it in ascending order.

I know the sorting buttons exist on the toolbar, but these users need
something simple and obvious, and doing it with one button would help.
Thanks!

Steve C

Tom,

Thanks much for the code. It will be very useful!
--
Steve C


"Tom Ogilvy" wrote:

Sub ABC()
Dim res As Variant
Dim rng As Range
Dim v As Long
Dim ord As Long
res = Application.Match("Project No.", Range("A1:IV1"), 0)
if iserror(res) then
msgbox "Column with header Project No. not found"
exit sub
end if
Set rng = Range(Cells(2, res), Cells(Rows.Count, res).End(xlUp))
v = Application.Evaluate("Sumproduct(--(" & rng.Offset(1, 0) _
.Resize(rng.Count - 1, 1).Address(0, 0) & "=" & _
rng.Resize(rng.Count - 1, 1).Address(0, 0) & "))")

ord = xlAscending
If v = rng.Count - 1 Then
ord = xlDescending
End If
rng.Resize(, 2).Sort _
Key1:=rng(1), _
Order1:=ord, _
Header:=xlNo
End Sub

worked for me. It assumes Project Name is to the left of Project No.

--
Regards,
Tom Ogilvy


"Steve C" wrote:

I have two columns in a spreadsheet labeled Project No. and Project Name. As
users continually add entries to those columns, I want to create a button
called "Sort by Project Number" at the top that toggles between ascending and
descending order (in other words, if the Project No. column is already sorted
in ascending order, then clicking the button sorts it in descending order,
and vice versa). If it's not sorted, then sort it in ascending order.

I know the sorting buttons exist on the toolbar, but these users need
something simple and obvious, and doing it with one button would help.
Thanks!

Steve C