Erratic Sort in Excel 97

[lurker]

Is there a known problem with the Sort method in Excel 97?

I have a command button to sort a set of data by name (alphabetical,
ascending, no header). I want to protect the header rows so I have
used the Worksheets Protect method with UserInterfaceOnly:=True in the
Workbook Open event. In Excel 2000 the following works :

Private Sub SortPatientList_Click()
On Error GoTo CleanUp

Application.ScreenUpdating = False
'PatientData has no headers
'The Name range is a col of the PatientData range
Range("PatientData").Sort Key1:=Range("Name")

CleanUp:
Application.ScreenUpdating = True
End Sub

In Excel 97 I have had to resort to removing the protection of the
sheet in the Workbook Open and:

Private Sub SortPatientList_Click()
On Error GoTo CleanUp

Application.ScreenUpdating = False

Range("PatientData").Select
Selection.Sort Key1:=Range("Name")

CleanUp:
Range("StartOfPatientData").Select
Application.ScreenUpdating = True
End Sub

because (a) the sort will not work with any protection on the sheet
(b) the sort will not work unless the range is selected first.

I have tried using a col as the Key1 (the range Name includes the
header) but this makes no difference. I have also tried the logical
solution of unprotecting the sheet before the sort but this also does
not work:

Private Sub SortPatientList_Click()
On Error GoTo CleanUp

Application.ScreenUpdating = False

Worksheets("Month Plan").Unprotect
Range("PatientData").Select
Selection.Sort Key1:=Range("Name")
'This should work but does not reliably!
'Range("PatientData").Sort Key1:=Range("Name")
Worksheets("Month Plan").Protect UserInterfaceOnly:=True

CleanUp:
Range("StartOfPatientData").Select
Application.ScreenUpdating = True
End Sub

Any ideas?

 

[Dave Peterson]

You can sort a range without selecting--if you're careful. My guess is you're
running your code from a control from the control toolbox toolbar and the code
is directly behind the sheet.

When you have an unqualified range in a General module, it refers to the
activesheet. When you have an unqualified range behind a worksheet, it refers
to the sheet holding the code.

And there are some things that your code still can't do even with
userinterfaceonly:=true set.

Option Explicit
Private Sub CommandButton1_Click()
Application.ScreenUpdating = False
With Worksheets("Month Plan")
.Unprotect Password:="hi"
.Range("PatientData").Sort _
Key1:=.Range("Name")
Application.Goto .Range("StartOfPatientData"), Scroll:=True
.Protect Password:="hi", userinterfaceonly:=True
End With

Application.ScreenUpdating = True
End Sub

(I wasn't sure where all those range names were, but it might give you a start.)