Trigger macro if value of linked cell changes.

R

Reeds

New Member
Joined
Mar 10, 2003
Messages
7
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?
 

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().
Sort by date Sort by votes
Hi Reeds,

Try this. Right click the sheet tab concerned and select View Code. Paste in the following code:
Code: 
Private Sub Worksheet_Calculate()
If Range("A2").Value = 6 Then MsgBox "Hello"
End Sub
Does that do what you want?
 
Upvote 0
Hi Richie,

the 5 and 6 is only an example.

The marco has to run if the value of A2 changes to any value.
 
Upvote 0
OK, try this:

In a general module (eg Module1):
Code: 
Public MyA2 As Variant
In the ThisWorkbook object:
Code: 
Private Sub Workbook_Open()
MyA2 = Sheet1.Range("A2").Value
End Sub
In the Sheet event code:
Code: 
Private Sub Worksheet_Calculate()
If Range("A2").Value <> MyA2 Then
    MyA2 = Range("A2").Value
    MsgBox "Hello - A2 changed"
End If
End Sub
HTH
 
Upvote 0
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")
 
Upvote 0
You must log in or register to reply here.

Similar threads

R
  • Question
VBA to run macro with the most recent events
Replies
4
Views
205
rafaelbatalha
R
C
  • Question
Macro that changes cell after each copy and paste
2
Replies
11
Views
436
PeteWright
PeteWright
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
437
Legacy 352679
L
B
  • Solved
Change existing code to attach range of cells to email
Replies
2
Views
239
bh24524
B
A
  • Question
I can't get private sub to run to update sheet name when the value in Cell A1 has changed
Replies
1
Views
337
duggie33
D

Forum statistics

Threads
1,215,473
Messages
6,125,020
Members
449,203
Latest member
tungnmqn90

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top