Cell Value Change to Trigger Macro Problem

[Tchamanian]

Im having some problems having the macro triggered by the value change on one of my cells.

The cell's value is not manually changed by user or changed by VBA code, rather, it is changed by an external feed that is transfered to excel via DLL (dynamic link library).

Basically what I am looking to acomplish is to store all historical values previously expressed in target cell below the target cell sequentially.

ex:
A1 = target change(live data feed from external dll.)
A2 = value expressed in cell A1 1-value ago
A3 = value expressed in cell A1 2-values ago
A4 = value expressed in cell A1 3-values ago and so on.

An Example code I am trying is as follows:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Range ("A1").Copy Destination:=("A2")
Range("A2").Select

Any help on this would be greatly appreciated!

Thank you!

 

[pedie]

Have you tried this way?

[/FONT]
[FONT=Courier New]Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
Application.EnableEvents = False
Range("A1").Copy Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
Application.EnableEvents = True
End If
End Sub

 

[Tchamanian]

Hey Pedie-

I tried the code you provided and was still presented with the same issue.
Prehaps I need to save the file as a macro-enabled workbook type?

Thanks for your reply.
RT

 

[pedie]

Hey Pedie-

I tried the code you provided and was still presented with the same issue.
Prehaps I need to save the file as a macro-enabled workbook type?

Thanks for your reply.
RT

Yes, save it as xls, or xlsm...and enable the macro of course
If you are using some other code to enter data A1 [ because i didnt get that dll thing.......] then it will not work if events are turned off in that macro...else it should be okay...

 

[Tchamanian]

Sweet!

The A1 cell doesnt have any code in excel. I've written an external program that basically gives a live feed of values to that particular cell that I would like to use excel to proceed with further calcs.