Worksheet Change Event
 

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.

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

--

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.

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.

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.




.

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.




.

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.




.



.

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.




.



.

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
-----Original Message-----
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.




.



.



.

wrote in message
...

SAP is annoying,

Amen to that