Sort code not working in 07

[pyrite]

Hi,

I have a simple function causing a major headache. I am using Excel 2007 and
have created a table which can be added to (the additions are done via code
attached to a button so they are always in same format). I have then placed a
control button at the top of each column to give the user an easy sort
function. Each button has the code to sort it's respective column. The
problem is that I have this working in 07 no problem but when an 03 user
opens the book and tries to run this code they get an error.

To solve this I redid the macro code in 03 and used this code instead. This
works fine in 07 until there are more than four lines and then I get an
error. Most confusing. To build the code I have basically 'recorded macro'
performed the necessary sort and pinched the code. Mainly because I don't
have enough knowledge to write from scratch.

My 07 code that won't work in 03 is:

Private Sub CommandButton1_Click()
Range("C8:G407").Select
ActiveSheet.Sort.SortFields.Clear
ActiveSheet.Sort.SortFields.Add Key:=Range("C8"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveSheet.Sort
.SetRange Range("C9:G407")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("D3").Select
End Sub


My 03 code that won't work in 07 is:

Range("C9:G407").Select
Selection.Sort Key1:=Range("C9"), Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal
End Sub

With the 03 code I get the error message 'Run Time Error 1004: Sort method
of Range class failed'

Both 07 and 03 users need to use this same book so I need a code that will
work in both, thanks for any help you can offer.

 

[Nick H]

Hi Pyrite,

I do a very similar thing in one of my workbooks. When the user clicks
on a header title I call the routine below from within the
SelectionChange event. It works fine in both 03 and 07 versions of
Excel.

Beware wrap-around...


Public Sub DoSort(ByRef rngData As Range, ByRef rngKey As Range,
Optional ByVal Hdr As Long, Optional ByVal Ordr As Long)
' Hdr...
' 0 = xlGuess
' 1 = xlYes
' 2 = xlNo

On Error Resume Next

If Ordr = 0 Then Ordr = 1 'xlAscending by default

rngData.Sort Key1:=rngKey, Order1:=Ordr, Header:=Hdr,
OrderCustom:=1, MatchCase:=False, _
Orientation:=xlTopToBottom, DataOption1:=xlSortTextAsNumbers

End Sub


Assuming that your CommandButton lives in the Worksheet module, you
would call this routine from your CommandButton#_Click routines like
so...

Call DoSort(Me.Range("C9:G407"), Me.Range("C9"))

HTH, Nick H