Hi Paul,

You say you've written two versions of the code, one that works in each
version of Excel. One way you could get around the problem would be to test
for the version of Excel that is being used and then write your two pieces of
code:

Dim intThisVersion As Integer
intThisVersion = int(Application.Version)
If intThisVersion = 10 Then '2002 version number

'Your 2002 code

ElseIf intThisVersion = 12 Then '2007 version number

'Your 2007 code

Else 'Other versions

End If


I don't have 2007 to hand right now, so I can't test the problem for myself,
sorry.

Sean.


"Paul D." wrote:

I recorded a macro to perform a sort of a single column on a worksheet then
rewrote the code to perform the same function when a button on a form is
pressed. This is the code I used to append a column in a worksheet with a
value of a textbox input and then sort the column:

Private Sub CommandButton1_Click()
'sngCountAns is the number of entries contained in the target column
strAddApt = Me.TextBox1.Value
If Len(strAddApt) = 4 Then
Sheets("Lists").Range("P" & sngCountAns + 1).Value = strAddApt
ActiveWorkbook.Worksheets("Lists").Sort.SortFields .Clear
ActiveWorkbook.Worksheets("Lists").Sort.SortFields .Add Key:=Range("P2"), _
SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Lists").Sort
.SetRange Range("P2:P" & sngCountAns + 1)
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
UserForm_Activate
Me.TextBox1.Value = ""
Else
MsgBox ("Airport ID must be 4 digits to be added to the list")
End If
End Sub

I am using windows vista and Excel 2007. When I press the button the
operations performs exactly as I want it to. The file is saved in
compatability mode. When the program is opened with a pc using Excel2002, the
sort functions generates errors when the CommandButton1 is pressed.

I rewrote the code so that it works in Excel 2002 but now it generates
errors when I run the program with Excel 2007.

Can anyone help me with code that will sort a single column in all versions
of excel from 97 to 2007?
Thanks.
Paul