Trigger macro if value of linked cell changes.

[Reeds]

I have the following problem:

Cell A1 gets a value through a ActiveX signal of another program.
The value changes every 30 seconds.

Cell A2 = Cell A1

What I want is the following:

If the value of A2 changes a macro has to run.
Problem is that there is no "enter" in A2 which triggers the macro.

Is there a solution? No values are entered by hand. All values are received through ActiveX.

To simulate the problem:

Cell A2 = Cell A1
Enter a value, let's say "5" in A1
A2 will change to "5"
Enter "6" in A1
A2 will change to "6"
If A2 changes to "6" a marco has to run e.g. message "It works" will appear.

Excel doesn't recognize the change of A2 because there was no value entered.

Do you know how to solve this?

 

[Richie(UK)]

Hi Reeds,

Try this. Right click the sheet tab concerned and select View Code. Paste in the following code:

Private Sub Worksheet_Calculate()
If Range("A2").Value = 6 Then MsgBox "Hello"
End Sub

Does that do what you want?

 

[Reeds]

Hi Richie,

the 5 and 6 is only an example.

The marco has to run if the value of A2 changes to any value.

 

[Richie(UK)]

OK, try this:

In a general module (eg Module1):

Public MyA2 As Variant

In the ThisWorkbook object:

Private Sub Workbook_Open()
MyA2 = Sheet1.Range("A2").Value
End Sub

In the Sheet event code:

Private Sub Worksheet_Calculate()
If Range("A2").Value <> MyA2 Then
    MyA2 = Range("A2").Value
    MsgBox "Hello - A2 changed"
End If
End Sub

HTH

 

[Reeds]

Thanks Richie,

this is what I was trying to do.

Now I can try the same in Access, because I had a simular problem in a Form. I want to log data if a value changes. Data is received through ActiveX signals.

(I also posted a question in "Access questions")