Home |
Search |
Today's Posts |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
wrote in message ... SAP is annoying, Amen to that |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
how to change the default font on the worksheet tabs? | Excel Discussion (Misc queries) | |||
Change position of move or copy worksheet option in Excel | Excel Discussion (Misc queries) | |||
change the orientation of a worksheet to Landscape | Excel Worksheet Functions | |||
Worksheet Row Change event | Excel Discussion (Misc queries) | |||
Change of Row event | Excel Discussion (Misc queries) |