That is one way, say

Private fChanged as Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

If Not fChanged Then
Me.Range("sortWT").Sort Key1:=Range("F1"), Order1:=xlAscending
fChanged = True
End If
End Sub


You could also test for changes in certain cells, such as sortWT, to stop
other changes firing it

Private fChanged as Boolean

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

On Error Goto ws_err:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("sortWT")) Is Nothing Then
If Not fChanged Then
Me.Range("sortWT").Sort Key1:=Range("F1"), Order1:=xlAscending
fChanged = True
End If
End If

ws_err:
Application.EnableEvents = True
End Sub

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
...
I have it as a button already, but just wanted to save
the user a bit of extra work! SAP opens the workbook but
I don't think the workbook open option would work as it
would fire before the data was loaded. I've tried the
following code in the worhsheet change event but this
fires everytime the active cell changes:-

Private Sub Worksheet_SelectionChange(ByVal Target As
Range)
Range("sortWT").Select
Selection.Sort Key1:=Range("F1"), Order1:=xlAscending
End Sub

I guess what I would need is a counter somewhere outside
of the procedure which, if 0, would stop the code within
the change event firing. Am I along the right lines??

-----Original Message-----
Aah, the mighty, damned annoying SAP!

It might depend upon how the data is acquired by the
workbook. It is
probably your only event choice (maybe Workbook_Open,
but your original post
suggest that the WB is already open). Bit of a cop-pout,
but why not just
try it, it should be easy to test.

The other option is to add a button to a toolbar, and
fire the sort macro
manually from there - is that an option?

--

HTH

RP
(remove nothere from the email address if mailing direct)


wrote in message
...
Hi Bob,

if only it was that easy. the other app is SAP and I
cannot change the way it works when it takes data
across
to MS Office. I just want to save the end user the
trouble of sorting the data once it drops into Excel.
Will Worksheet_Change do the trick?

regards,

TonyM

-----Original Message-----
Why don't you just tag the sort onto the end of the
other application?

--

HTH

RP
(remove nothere from the email address if mailing
direct)


"TonyM" wrote in
message
...
Hi,
I need an automatic sort on a data table. I have a
program in another application which automatically
loads
data into an Excel spreadsheet. How can I use a
worksheet
change event to automatically sort the data when
this
happens?

Thanks in anticipation.




.



.