Run Macro

[Johanf]

Hi I need to run a macro every time the value of a certain cell changes from 0 to 1 The value comes from a dde link.(production machine )
When the value changes from 0 to 1 i need to timestamp a cell then insert a row above , wait for the next change and do the same again.
Will that be possible?

[mdmackillop]

Hi Johan
Welcome to VBAX
Here's the code from a KB item which is awaiting approval.
Option Explicit
'Create variable to hold values
Dim Monitored

Private Sub Worksheet_Activate()
Monitored = Range("E8").Value 'Read in value prior to any changes
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
'Check target to determine if macro is triggered
If Intersect(Target, Union(Range("E6"), Range("E7"))) Is Nothing Then Exit Sub
'Prevent looping of code due to worksheet changes
Application.EnableEvents = False
'Compare monitored cell with initial value
If Range("E8").Value <> Monitored Then
'Do things as a result of a change
DoThings
'Reset Variable with new monitored value
Monitored = Range("E8").Value
End If
'Reset events
Application.EnableEvents = True
End Sub

Private Sub DoThings()
With Range("E9")
.Formula = Range("E6") + Range("E7")
If .Interior.ColorIndex = 6 Then
.Interior.ColorIndex = 8
Else: .Interior.ColorIndex = 6
End If
End With
End Sub

[Bob Phillips]

Why would you use



If Intersect(Target, Union(Range("E6"), Range("E7"))) Is Nothing Then Exit Sub


and not



If Intersect(Target, Range("E6:E7")) Is Nothing Then Exit Sub


and why



.Formula = Range("E6") + Range("E7")


and not



.Value = Range("E6") + Range("E7")

[Johanf]

Hi mdmackillop,
Thank you for the reply.
Here is what i need . I do not know vba well at all. The cell a1 update through the link to my machine. I think it is called a hot link. as soon as A1 is 1 i want to run Macro 2 Will that be possible?

[Bob Phillips]

One way would be to add a link to cell A1 in another cell, =A1, and use worksheet calculate to trap the update and un the macro. The problem is that the macro would run every time a calculate happens, which can be frequent if you have other formulae.

Would that be a problem?

[Johanf]

Hi xld,
Thans for the reply.
No that will not be a problem as i can program my PLC so that i only run the macro if the machine stans for more than 3 minutes but i do not realy know vba well and do not know how to create the code.

[Bob Phillips]

Okay, link a cell to A1 as I said then add this code to your worksheet.

Make sure that Macro2 is in a standard code module or the same worksheet code module.



Private Sub Worksheet_Calculate()
If Me.Range("A1").Value = 2 Then
Call Macro2
End If
End Sub


This is worksheet event code, which means that it needs to be
placed in the appropriate worksheet code module, not a standard
code module. To do this, right-click on the sheet tab, select
the View Code option from the menu, and paste the code in.