Run a macro based on event

B

Bret

I would like to run a macro based on an event either by calling it from a cell based on an value change (DDE request is monitoring a status word; seconds on a clock) or by setting up an time delay that calls a macro every minute or so

Any idea what the syntax/solution would be for this?
 
B

Bob Phillips

Apparently, a DDE change does not trigger a change event. I think that you
have to link other cells to your DDE Cells so that they also get updated
when DDE updates, but triggering the event.

Can you try something else? Link say Z4:Z10 to I4:I10, each one in turn,
where I4:I10 are the DDE cells.

Then use this event code

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("Z4:Z10")) Is Nothing Then
With Target
If .Value <> "" Then
.offset(0, -16).Value = Format(Now, "dd mmm yyyy hh:mm:ss")
End If
End With
End If
ws_exit:
Application.EnableEvents = True
End Sub

What I am trying is to force the DDE updates to also update the range
Z4:Z10. This will (might?) trigger the change event, and we trap the
secondary range, and insert the date from there. I can't test it as I don't
have the DDE updates.



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

Bret said:
I would like to run a macro based on an event either by calling it from a
Click to expand...
cell based on an value change (DDE request is monitoring a status word;
seconds on a clock) or by setting up an time delay that calls a macro every
minute or so.
 
T

Tom Ogilvy

Just to add to Bob's advice,

Linking cells will trigger the calculate event, not the change event.

DDE will trigger the change event in Excel 2000 and later to the best of my
knowledge and experience.

the SetLinkOnData method will assign a macro to fire for a specific DDE
link. This should work for xl5 and later

If you want to trigger at spaced intervals look at Chip Pearson's page

http://www.cpeason.com/excel/ontime.htm
 
Top