| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Adding rows to create list from DDE data
I have data updated via a DDE link and each time the DDE link updates I need to capture the data so that it creates a list by adding a new row. I have used the SheetCalculateEvent to trigger the update to cell B2 (which links to another cell which is the DDE link) which then invokes the SheetChangeEvent.
My problem is that on each update a new row is inserted at the top of the list for the new data and this causes the screen to flicker as all the data is moved. I would rather have the data just build progressively so that the new data is added to the bottom of the list. Can someone help how best to achieve this? My code is Private Sub Worksheet_Calculate( Worksheet_Change Range("$B$2" End Su Private Sub Worksheet_Change(ByVal Target As Range If Target.address = "$B$2" The Sheets("intraday").Selec Range("A2:E2").Selec Selection.Cop Range("A4").Selec Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=Fals If Range("B4") Range("B5") The Range("I10").Selec Selection.EntireRow.Inser Range("A4:H4").Selec Selection.Cop Range("I10").Selec Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=Fals Range("A4:E4").Selec Selection.Cop Range("A5").Selec Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks :=False, Transpose:=Fals End I End I End Su I should explain that I copy the range A2..E2 (which contains the target cell) to A4..E4, and this is then compared to the same data prior to the last update which is contained at A5..E5 to do some extra calcs in F5..H5. If the new update at B4 is than the last update at B5, then the new update is added to the list at I10..P10 Any help gratefully received Thanks, Simon |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding rows to create list from DDE data
Simon,
You could bracket your code with Application.ScreenUpdating = False : : Application.ScreenUpdating = True and remove all the Select statements (much neater), viz. If Target.address = "$B$2" Then Application.ScreenUpdating = False With Sheets("intraday") .Range("A2:E2").Copy .Range("A4").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False If .Range("B4") .Range("B5") Then .Rows(10).Insert .Range("A4:H4").Copy .Range("I10").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False .Range("A4:E4").Copy .Range("A5").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End If End With Application.ScreenUpdating = False End If -----Original Message----- I have data updated via a DDE link and each time the DDE link updates I need to capture the data so that it creates a list by adding a new row. I have used the SheetCalculateEvent to trigger the update to cell B2 (which links to another cell which is the DDE link) which then invokes the SheetChangeEvent. My problem is that on each update a new row is inserted at the top of the list for the new data and this causes the screen to flicker as all the data is moved. I would rather have the data just build progressively so that the new data is added to the bottom of the list. Can someone help how best to achieve this? My code is: Private Sub Worksheet_Calculate() Worksheet_Change Range("$B$2") End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.address = "$B$2" Then Sheets("intraday").Select Range("A2:E2").Select Selection.Copy Range("A4").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False If Range("B4") Range("B5") Then Range("I10").Select Selection.EntireRow.Insert Range("A4:H4").Select Selection.Copy Range("I10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A4:E4").Select Selection.Copy Range("A5").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If End Sub I should explain that I copy the range A2..E2 (which contains the target cell) to A4..E4, and this is then compared to the same data prior to the last update which is contained at A5..E5 to do some extra calcs in F5..H5. If the new update at B4 is than the last update at B5, then the new update is added to the list at I10..P10 Any help gratefully received Thanks, Simon . |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Adding rows to create list from DDE data
Kevin
Thanks a million. Have tried your suggestion and it works a treat. Many thanks, Simon "Kevin Beckham" wrote in message ... Simon, You could bracket your code with Application.ScreenUpdating = False : : Application.ScreenUpdating = True and remove all the Select statements (much neater), viz. If Target.address = "$B$2" Then Application.ScreenUpdating = False With Sheets("intraday") .Range("A2:E2").Copy .Range("A4").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False If .Range("B4") .Range("B5") Then .Rows(10).Insert .Range("A4:H4").Copy .Range("I10").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, _ SkipBlanks:=False, Transpose:=False .Range("A4:E4").Copy .Range("A5").PasteSpecial Paste:=xlPasteValues, _ Operation:=xlNone, SkipBlanks:=False, _ Transpose:=False End If End With Application.ScreenUpdating = False End If -----Original Message----- I have data updated via a DDE link and each time the DDE link updates I need to capture the data so that it creates a list by adding a new row. I have used the SheetCalculateEvent to trigger the update to cell B2 (which links to another cell which is the DDE link) which then invokes the SheetChangeEvent. My problem is that on each update a new row is inserted at the top of the list for the new data and this causes the screen to flicker as all the data is moved. I would rather have the data just build progressively so that the new data is added to the bottom of the list. Can someone help how best to achieve this? My code is: Private Sub Worksheet_Calculate() Worksheet_Change Range("$B$2") End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.address = "$B$2" Then Sheets("intraday").Select Range("A2:E2").Select Selection.Copy Range("A4").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False If Range("B4") Range("B5") Then Range("I10").Select Selection.EntireRow.Insert Range("A4:H4").Select Selection.Copy Range("I10").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False Range("A4:E4").Select Selection.Copy Range("A5").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False End If End If End Sub I should explain that I copy the range A2..E2 (which contains the target cell) to A4..E4, and this is then compared to the same data prior to the last update which is contained at A5..E5 to do some extra calcs in F5..H5. If the new update at B4 is than the last update at B5, then the new update is added to the list at I10..P10 Any help gratefully received Thanks, Simon . |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Automatically extend a list when adding a new data to another list | Excel Discussion (Misc queries) | |||
| create 1 master list from a combination of rows and columns | Excel Discussion (Misc queries) | |||
| Adding data to create ranges | Excel Worksheet Functions | |||
| Want to Create a List in Excel 2002; Don't see List in Data Menu? | Excel Discussion (Misc queries) | |||
| Does Excel 2002 have a List>Create List option under Data? | Excel Discussion (Misc queries) | |||
Linear Mode
