Worksheet Change Event

[TonyM]

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.

 

[Bob Phillips]

Why don't you just tag the sort onto the end of the

--

HTH

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

 

[Bob Phillips]

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)

 

[Guest]

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

 

[Bob Phillips]

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)

 

[Guest]

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??

 

[Bob Phillips]

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)

 

[Guest]

Hi Bob,

thanks for this - it looks like the answer !!
SAP is annoying, it allows you to filter, sort etc. when
the data is on screen in SAP but as soon as you elect to
transfer to MS Excel all the criteria you specified in
SAP are lost !!

Regards,

TonyM

 

[Bob Phillips]

SAP is annoying,

Amen to that