Sort list by selecting header cell?

[Bassman62]

Using Excel 2007 on WinXP
How can I sort a list simply by selecting one of the header cells?
I have a list that includes unlocked cells and locked cells with formulas.
The file is used by several users and the sheet is protected to prevent
changes to the formulas.
However, the users are required to sort the list after adding to it and this
is not allowed for locked cells on a protected sheet.
I have a macro that unprotects the sheet, sorts the list keying on the
active cell, then protects the sheet. It requires the user to select a cell
within the list then click on a control button to activate the macro.
How can I accomplish the same operation not with a command button but by
simply selecting one of the header cells?
Thanks much.
Below is the code I have:

Private Sub CommandButton1_Click()
' Sort list using the column of the active cell as the sort key
Dim SortKey1 As String
SortKey1 = Range("headers").Columns(ActiveCell.Column -
(Range("headers").Column - 1))
If Not Intersect(ActiveCell, Range("JobList")) Is Nothing Then
ActiveSheet.Unprotect ' Unprotect the active sheet
Range("JobList").Sort Key1:=SortKey1, Order1:=xlAscending,
Header:=xlYes
' Protect the active sheet without a password
ActiveSheet.Protect DrawingObjects:=False, _
Contents:=True, Scenarios:=False
End If
End Sub

 

[Jim Cone]

You could add a clear rectangle that fills the header cell.
Attach your sub to the rectangle.
Or you could use the Worksheet_BeforeDoubleClick event to run the code.
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)




"Bassman62"
<[email protected]>
wrote in message
Using Excel 2007 on WinXP
How can I sort a list simply by selecting one of the header cells?
I have a list that includes unlocked cells and locked cells with formulas.
The file is used by several users and the sheet is protected to prevent
changes to the formulas.
However, the users are required to sort the list after adding to it and this
is not allowed for locked cells on a protected sheet.
I have a macro that unprotects the sheet, sorts the list keying on the
active cell, then protects the sheet. It requires the user to select a cell
within the list then click on a control button to activate the macro.
How can I accomplish the same operation not with a command button but by
simply selecting one of the header cells?
Thanks much.
Below is the code I have:

Private Sub CommandButton1_Click()
' Sort list using the column of the active cell as the sort key
Dim SortKey1 As String
SortKey1 = Range("headers").Columns(ActiveCell.Column -
(Range("headers").Column - 1))
If Not Intersect(ActiveCell, Range("JobList")) Is Nothing Then
ActiveSheet.Unprotect ' Unprotect the active sheet
Range("JobList").Sort Key1:=SortKey1, Order1:=xlAscending,
Header:=xlYes
' Protect the active sheet without a password
ActiveSheet.Protect DrawingObjects:=False, _
Contents:=True, Scenarios:=False
End If
End Sub

 

[Norman Jones]

Hi Bassman,

I would follow Jim's advice and use a shape's
click event or the Worksheet's BeforeDoubleClick
event.

If, however, as you indicate, you wish to launch
the sort operation in response to the selection
of a header cell, try:

'==========>>
Private Sub Worksheet_SelectionChange _
(ByVal Target As Range)
Dim Rng As Range

Set Rng = Me.Range("headers")

If Not Intersect(Rng, Target) Is Nothing Then
Call mySortMacro 'CommandButton1_Click
End If

End Sub
'<<==========

This is event code and should be pasted
into the worksheets's code module (not a
standard module and not the workbook's
ThisWorkbook module):

Right-click the worksheet's tab |
Select 'View Code' from the menu
Paste the code
Alt-F11 to return to Excel.

 

[Bassman62]

Thanks to you both.
I liked the "Worksheet_SelectionChange ..." method
Works like a charm.
Thanks again!

 

[Mike Fogleman]

Click_Sort example works on columns A thru D, change the range of columns to
suit:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A11")) Is Nothing Then
With Target
If .Characters(Len(.Value), 1).Font.Name <> "Marlett" Then
.Value = .Value & " t"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
End If
If Right(.Value, 1) = "t" Then
.EntireColumn.Sort Key1:=.Offset(1, 0), _
Order1:=xlAscending, _
HEADER:=xlYes
.Value = Left(.Value, Len(.Value) - 1) & "u"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
Else
.EntireColumn.Sort Key1:=.Offset(1, 0), _
Order1:=xlDescending, _
HEADER:=xlYes
.Value = Left(.Value, Len(.Value) - 1) & "t"
.Characters(Len(.Value), 1).Font.Name = "Marlett"
End If
.Offset(1, 0).Activate
End With
End If

ws_exit:
Application.EnableEvents = True
End Sub

Mike F