Automatic Value Change

A

Alectrical

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
 
P

Patrick Molloy

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.
 
M

Mike H

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
 
A

Alectrical

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top