INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

VBA Visual Basic for Applications (Microsoft) FAQ

Office / VBA General

Sort a data range by double-clicking header cell by VBAjedi
Posted: 25 Feb 04

Many websites offer the ability to click on a column header in a data grid to resort the grid by that column, and users have grown to expect this. Now you can emulate this behavior in Excel.

This routine will sort on up to three columns, in the order that the user double-clicks on the data column headers (I chose not to trigger it when the user double-clicks a cell within the actual data area, but you could easily change that). It requires three things:

1) Two named ranges on your sheet. One for the Data area (not including headers), and one for the Headers. I called mine "DataArea" and "HeaderArea". I know, I know - pure genius.
2) A public array variable named "SortArr()". Put the following at the top of any normal Module:
Public SortArr(3)
3) A button to call the "ResetArr()" sub (so the user can reset the criteria).

Here's the code (watch out for line-wrap!). Put it in the desired sheets code area (right-click sheet tab -> view code) and change the sheet name in line 3 to match:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, Cancel As Boolean)
Dim MyTarget As Range, x As Variant
Set ShRe = Worksheets("MySheetName")
Set MyTarget = Intersect(Target.Cells(1, 1), ShRe.Range("HeaderArea"))
If Not MyTarget Is Nothing Then
   If SortArr(0) < 3 Then ' There is room for another criteria
      x = SetArr(MyTarget.Column)
      Select Case SortArr(0)
      Case 1
         ShRe.Range("DataArea").Sort Key1:=Cells(1, SortArr(1)), Order1:=xlAscending, _
            Orientation:=xlTopToBottom
      Case 2
         ShRe.Range("DataArea").Sort Key1:=Cells(1, SortArr(1)), Order1:=xlAscending, _
            Key2:=Cells(1, SortArr(2)), Order2:=xlAscending, _
            Orientation:=xlTopToBottom
      Case 3
         ShRe.Range("DataArea").Sort Key1:=Cells(1, SortArr(1)), Order1:=xlAscending, _
            Key2:=Cells(1, SortArr(2)), Order2:=xlAscending, _
            Key3:=Cells(1, SortArr(3)), Order3:=xlAscending, _
            Orientation:=xlTopToBottom
      Case Else ' Defaults to sort on first column
         ShRe.Range("DataArea").Sort Key1:=Cells(1, 1), Order1:=xlAscending, _
            Orientation:=xlTopToBottom
      End Select
   Else
      MsgBox "You have already reached the maximum of 3 criteria."
   End If
   Cancel = True ' Cancels default double-click behavior
End If
End Sub

Function SetArr(SortByCol)
Dim x As Integer, Flag As Boolean
Flag = False
For x = 1 To 3
   If SortArr(x) = 0 Then
      SortArr(x) = SortByCol
      SortArr(0) = x ' Set criteria count
      Flag = True
      Exit For
   End If
Next x
SetArr = Flag
End Function

Sub ResetArr()
Dim x As Integer
For x = 0 To 3
   SortArr(x) = 0
Next x
End Sub

By adding a second dimension to the array to store SortOrder, and testing in SetArr to see if that column is already selected, you could modify it to allow the sort order to be reverse if the column is clicked again.

Have fun!


Back to VBA Visual Basic for Applications (Microsoft) FAQ Index
Back to VBA Visual Basic for Applications (Microsoft) Forum

My Archive

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close