Creating
 

Hello All,
I have a worksheet of data with the following characteristics:
--column headers
--numeric and textual data
--a "frozen" pane
--some columns in which input is constrained by validation lists stored on
other worksheets within the same Excel file

What I need to do is make it so that each of the main worksheet's column
headers present my Excel-newbie end-users with a simple dropdown/pulldown
that allows them to sort (ascending and descending) all the rows across all
of the columns, i.e. across the frozen pane line, across the validation-list
columns, etc. In an ideal world, users would be able to sort by multiple
columns, i.e. by A then B then C, but I'll happily settle for sorting by one
column.

For the life of me, I can't seem to figure this out. Thanks in advance for
any help -- it's much appreciated.

Jack

Creating
 

How about this. You put some rectangles over each of the header cells in row
1. Make them invisible. When the user clicks on one of those rectangles, your
data gets sorted by that column (click on the same rectangle, it sorts in the
opposite direction).

If that sounds promising...

Saved from a previous post...

I put rectangles over the headers (made the borders invisible) so that when you
clicked on the rectangle, it looked like you were clicking on the header.

Option Explicit
Sub setupOneTime()

Dim myRng As Range
Dim myCell As Range
Dim curWks As Worksheet
Dim myRect As Shape

Set curWks = ActiveSheet

With curWks
'10 columns
Set myRng = .Range("a1").Resize(1, 10)
For Each myCell In myRng.Cells
With myCell
Set myRect = .Parent.Shapes.AddShape _
(Type:=msoShapeRectangle, _
Top:=.Top, Height:=.Height, _
Width:=.Width, Left:=.Left)
End With
With myRect
.OnAction = ThisWorkbook.Name & "!SortTable"
.Fill.Visible = False
.Line.Visible = False
End With
Next myCell
End With
End Sub
Sub sortTable()

Dim myTable As Range
Dim myColToSort As Long
Dim curWks As Worksheet
Dim mySortOrder As Long
Dim LastRow As Long

Set curWks = ActiveSheet
With curWks
myColToSort = .Shapes(Application.Caller).TopLeftCell.Column
LastRow = .Cells(.Rows.Count, "a").End(xlUp).Row
Set myTable = .Range("a1:a" & LastRow).Resize(, 10)
If .Cells(myTable.Row + 1, myColToSort).Value _
< .Cells(LastRow, myColToSort).Value Then
mySortOrder = xlDescending
Else
mySortOrder = xlAscending
End If
myTable.Sort key1:=.Cells(myTable.Row, myColToSort), _
order1:=mySortOrder, _
header:=xlYes
End With

End Sub

===
The setuponetime routine puts the rectangles in 10 columns A1:J1 with this:
'10 columns
Set myRng = .Range("a1").Resize(1, 10)

The sortTable routine sorts that same table based on the number of cells used in
column A (and 10 columns wide) with this line:

Set myTable = .Range("a1:a" & LastRow).Resize(, 10)

If you have more or less columns, adjust both .resize(,10) portions.

If you have to use a different column to get the last row, adjust this line:
LastRow = .Cells(.Rows.Count, "a").End(xlUp).Row


ActualSelf wrote:

Hello All,
I have a worksheet of data with the following characteristics:
--column headers
--numeric and textual data
--a "frozen" pane
--some columns in which input is constrained by validation lists stored on
other worksheets within the same Excel file

What I need to do is make it so that each of the main worksheet's column
headers present my Excel-newbie end-users with a simple dropdown/pulldown
that allows them to sort (ascending and descending) all the rows across all
of the columns, i.e. across the frozen pane line, across the validation-list
columns, etc. In an ideal world, users would be able to sort by multiple
columns, i.e. by A then B then C, but I'll happily settle for sorting by one
column.

For the life of me, I can't seem to figure this out. Thanks in advance for
any help -- it's much appreciated.

Jack

--

Dave Peterson