Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
TonyM
 
Posts: n/a
Default 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.


  #2   Report Post  
Bob Phillips
 
Posts: n/a
Default

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.




  #3   Report Post  
Bob Phillips
 
Posts: n/a
Default

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.




  #4   Report Post  
 
Posts: n/a
Default

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.




.

  #5   Report Post  
Bob Phillips
 
Posts: n/a
Default

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.




.





  #6   Report Post  
 
Posts: n/a
Default

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.




.



.

  #7   Report Post  
Bob Phillips
 
Posts: n/a
Default

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.




.



.



  #8   Report Post  
 
Posts: n/a
Default

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.




.



.



.

  #9   Report Post  
Bob Phillips
 
Posts: n/a
Default


wrote in message
...

SAP is annoying,


Amen to that


Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
how to change the default font on the worksheet tabs? Mark Dvorkin Excel Discussion (Misc queries) 3 March 8th 05 04:55 AM
Change position of move or copy worksheet option in Excel JesseAviles Excel Discussion (Misc queries) 1 February 22nd 05 11:25 PM
change the orientation of a worksheet to Landscape epoliver Excel Worksheet Functions 2 December 31st 04 09:46 PM
Worksheet Row Change event crazybass2 Excel Discussion (Misc queries) 4 December 8th 04 06:29 PM
Change of Row event crazybass2 Excel Discussion (Misc queries) 7 December 7th 04 07:21 PM


All times are GMT +1. The time now is 07:51 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"