Sort Protected Sheet with Header Columns

[TJV]

I'm trying to get a spreadsheet to sort by clicking on the Header
cell. I found the following code and it works when the sheet is
unprotected:

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Application.EnableEvents = False
If Not Intersect(ActiveCell, _
Range("A13:AT13")) Is Nothing Then 'Range with column headings
ActiveCell.CurrentRegion.Sort Key1:=ActiveCell, Header:=xlYes
End If
Application.EnableEvents = True
End Sub


But, when I protect the sheet and try to run the following code,
nothing happens.

Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

Application.EnableEvents = False
If Not Intersect(ActiveCell, _
Range("A13:AT13")) Is Nothing Then 'Range with column headings
ActiveSheet.Unprotect Password:="test"
ActiveCell.CurrentRegion.Sort Key1:=ActiveCell, Header:=xlYes
ActiveSheet.Protect Password:="test"
End If
Application.EnableEvents = True
End Sub

What am I doing wrong? Can this even be done?

I'm running Excel 97. Any help would be great.

Thanks!

 

[Dave Peterson]

Your code worked ok for me (I used xl2002). But I don't recall anything in your
call that would not work in xl97.

But I think I would have referred to the ranges/worksheet slightly differently:

Option Explicit
Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)

If Target.Cells.Count > 1 Then Exit Sub

Application.EnableEvents = False
If Not Intersect(Target, _
Range("A13:AT13")) Is Nothing Then 'Range with column headings
Me.Unprotect Password:="test"
Target.CurrentRegion.Sort Key1:=Target, Header:=xlYes
Me.Protect Password:="test"
End If
Application.EnableEvents = True

End Sub

And is there any chance you turned off application.enableevents?

Hit ctrl-G in the VBE and type this in:

?application.enableevents
Just to see what it is.

and
application.enableevents = true
(if it was false.)