I have data in A1 that changes value every second, via a DDE link, I need to
record this data in column B, where when A1 changes the new value is put in a
new row in column B. The following code works only when I substitute
Worksheet_Change with Selection_Change and provided I select A1. I need this
process to be automatic, any ideas.

Thanks
Alec

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, LR As Long
If Target.Address(0, 0) = "A1" Then
LR = Cells(Rows.Count, "B").End(xlUp).Row
If Not (LR = 1 And Len(Cells(LR, "B").Value) = 0) Then LR = LR + 1
Application.EnableEvents = False
Cells(LR, "B").Value = Range("A1").Value
Application.EnableEvents = True
End If

End Sub

it suggests that the DDE update doesn't fire a change event? Is there any
event associated with the dde control at all - the documentation will say
something about this i expect?
Otherwise, make sure that the workbook's calculation mode is automatic and
see if this gets fired when the dde updates.

"Alectrical" wrote:

I have data in A1 that changes value every second, via a DDE link, I need to
record this data in column B, where when A1 changes the new value is put in a
new row in column B. The following code works only when I substitute
Worksheet_Change with Selection_Change and provided I select A1. I need this
process to be automatic, any ideas.

Thanks
Alec

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, LR As Long
If Target.Address(0, 0) = "A1" Then
LR = Cells(Rows.Count, "B").End(xlUp).Row
If Not (LR = 1 And Len(Cells(LR, "B").Value) = 0) Then LR = LR + 1
Application.EnableEvents = False
Cells(LR, "B").Value = Range("A1").Value
Application.EnableEvents = True
End If

End Sub

Hi,

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, LR As Long
If Target.Address = "$A$1" Then
LR = Cells(Rows.Count, "B").End(xlUp).Row
If Not (LR = 1 And Len(Cells(LR, "B").Value) = 0) Then LR = LR + 1
Application.EnableEvents = False
Cells(LR, "B").Value = Range("A1").Value
Application.EnableEvents = True
End If
End Sub

Mike

"Alectrical" wrote:

I have data in A1 that changes value every second, via a DDE link, I need to
record this data in column B, where when A1 changes the new value is put in a
new row in column B. The following code works only when I substitute
Worksheet_Change with Selection_Change and provided I select A1. I need this
process to be automatic, any ideas.

Thanks
Alec

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, LR As Long
If Target.Address(0, 0) = "A1" Then
LR = Cells(Rows.Count, "B").End(xlUp).Row
If Not (LR = 1 And Len(Cells(LR, "B").Value) = 0) Then LR = LR + 1
Application.EnableEvents = False
Cells(LR, "B").Value = Range("A1").Value
Application.EnableEvents = True
End If

End Sub

Hi Mike
Thanks for your reply.

The code you offered only works when I double click cell A1, then click on
any other cell. It does not work automatically, I have automatic calculate
(F9) option checked.

"Mike H" wrote

Hi,

Try this

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, LR As Long
If Target.Address = "$A$1" Then
LR = Cells(Rows.Count, "B").End(xlUp).Row
If Not (LR = 1 And Len(Cells(LR, "B").Value) = 0) Then LR = LR + 1
Application.EnableEvents = False
Cells(LR, "B").Value = Range("A1").Value
Application.EnableEvents = True
End If
End Sub

Mike

"Alectrical" wrote:

I have data in A1 that changes value every second, via a DDE link, I need to
record this data in column B, where when A1 changes the new value is put in a
new row in column B. The following code works only when I substitute
Worksheet_Change with Selection_Change and provided I select A1. I need this
process to be automatic, any ideas.

Thanks
Alec

Private Sub Worksheet_Change(ByVal Target As Range)
Dim X As Long, LR As Long
If Target.Address(0, 0) = "A1" Then
LR = Cells(Rows.Count, "B").End(xlUp).Row
If Not (LR = 1 And Len(Cells(LR, "B").Value) = 0) Then LR = LR + 1
Application.EnableEvents = False
Cells(LR, "B").Value = Range("A1").Value
Application.EnableEvents = True
End If

End Sub