VBA code for triggering a macro based on change in cell with formula

%3CLINGO-SUB%20id%3D%22lingo-sub-2527369%22%20slang%3D%22en-US%22%3EVBA%20code%20for%20triggering%20a%20macro%20based%20on%20change%20in%20cell%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2527369%22%20slang%3D%22en-US%22%3E%3CP%3EI'm%20trying%20to%20get%20a%20macro%20to%20run%20based%20on%20a%20change%20in%20value%20in%20a%20cell%2C%20but%20the%20cell%20contains%20with%20a%20formula%20so%20the%20standard%20trigger%20on%20value%20does%20not%20work.%26nbsp%3B%20Any%20suggestions%20%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-2527369%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2527576%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20for%20triggering%20a%20macro%20based%20on%20change%20in%20cell%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2527576%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F1098460%22%20target%3D%22_blank%22%3E%40Th0r_L13n%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EThe%20best%20option%20is%20to%20check%20the%20cells%20that%20determine%20the%20result%20of%20the%20formula%20and%20that%20are%20directly%20changed%20by%20the%20user.%3C%2FP%3E%0A%3CP%3EA%20very%20simple%20example%3A%20let's%20say%20that%20cell%20A10%20contains%20the%20formula%20%3DSUM(A2%3AA9)%2C%20and%20that%20A2%3AA9%20are%20filled%20by%20the%20user.%20Instead%20of%20checking%20A10%2C%20check%20A2%3AA9%3A%3C%2FP%3E%0A%3CPRE%20class%3D%22lia-code-sample%20language-visual%22%3E%3CCODE%3EPrivate%20Sub%20Worksheet_Change(ByVal%20Target%20As%20Range)%0A%20%20%20%20If%20Not%20Intersect(Range(%22A2%3AA9%22)%2C%20Target)%20Is%20Nothing%20Them%0A%20%20%20%20%20%20%20%20...%0A%20%20%20%20End%20If%0AEnd%20Sub%3C%2FCODE%3E%3C%2FPRE%3E%0A%3CP%3EAlternatively%2C%20you%20could%20use%20the%20Worksheet_Calculate%20event.%20This%20will%20cause%20a%20lot%20of%20overhead%2C%20though%2C%20for%20this%20event%20does%20not%20have%20a%20Target%20argument.%20So%20the%20code%20will%20run%20every%20time%20any%20cell%20on%20the%20sheet%20is%20recalculated.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-2527854%22%20slang%3D%22en-US%22%3ERe%3A%20VBA%20code%20for%20triggering%20a%20macro%20based%20on%20change%20in%20cell%20with%20formula%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-2527854%22%20slang%3D%22en-US%22%3EThe%20problem%20is%20that%20the%20formula%20comes%20from%20a%20external%20feed%2C%20and%20not%20a%20sum%20or%20so%20from%20other%20cells%3CBR%20%2F%3E%3C%2FLINGO-BODY%3E
New Contributor

I'm trying to get a macro to run based on a change in value in a cell, but the cell contains with a formula so the standard trigger on value does not work.  Any suggestions ?

4 Replies

@Th0r_L13n 

The best option is to check the cells that determine the result of the formula and that are directly changed by the user.

A very simple example: let's say that cell A10 contains the formula =SUM(A2:A9), and that A2:A9 are filled by the user. Instead of checking A10, check A2:A9:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Range("A2:A9"), Target) Is Nothing Them
        ...
    End If
End Sub

Alternatively, you could use the Worksheet_Calculate event. This will cause a lot of overhead, though, for this event does not have a Target argument. So the code will run every time any cell on the sheet is recalculated.

The problem is that the formula comes from a external feed, and not a sum or so from other cells
example :
=QueryJoule("Italy Baseload - All Venues", "", "1", "bid price", "1", "Quarters")
and the feed ends up to be a number

@Th0r_L13n 

You'll have to use the Worksheet_Calculate event.