Call Macro dynamically when cell value changed by formula

Question

In Excel, through "Data connection", I am getting live data in cell A1. Using A1's value, I am doing some calculation on cell B1. If the value in B1 is TRUE, then that value should be copied to C1.

I have so far been unable to achieve the desired result and would appreciate some guidance.

Wouldn't a simple =IF formula in C1 suffice ? Like =IF(B1=TRUE,A1,""). — Vulthil, Jan 11 '16 at 12:30
Not really clear what kind of link it is, but the `Workbook.SetLinkOnData` method might be what you need. — Rory, Jan 11 '16 at 12:41

score 0 · Accepted Answer · answered Jan 11 '16 at 13:33

If you are refreshing cell A1 externally and want to perform the copy based on cell B1's calculation, then you can use the Calculate event macro.

Say we want the first value in A1 that exceeds 100 to be captured in C1. Once the first value has been captured, no other values above 100 will be copied.

In B1 we enter:

=AND(A1>100,C1="")

and then install this event macro in the worksheet code area:

Private Sub Worksheet_Calculate()
   Dim B As Boolean
   B = Range("B1").Value
   If B Then
      Application.EnableEvents = False
         Range("C1").Value = Range("A1").Value
      Application.EnableEvents = True
   End If
End Sub

Call Macro dynamically when cell value changed by formula

1 Answers1