Results 1 to 4 of 4

Thread: VBA Run macro on cell value change from formula

  1. #1

    Default VBA Run macro on cell value change from formula

    I want to fire a macro when the value in a cell changes i.e when the result in cell C1 changes

    A B C
    1 5 4 =A1*B1

    private sub worksheet_change(byval target as range)

    if target.address = [c1].address then
    Msgbox([A1] & " times " & [b1] & " equals " & [C1])
    end if

    End sub

    This macro fires when you click in C1 and change the formula or do something, but not when the result changes e.g. changing A1 to 6 does not activate the macro.

    In this case it's relatively easy - just target cells A1 and B1. Unfortunately my sheet is pulling in realtime price data (from bloomberg if you must know) so A1 and B1 will change value without ever being activated and the usual Worksheet_change event doesn't occur. You would encounter the same problems if you were using volatile functions such as =Rand() or pulling in data from other workbooks where it becomes hard to track which cells should be the triggers; which makes me think there must be a solution out there somewhere.
    I am therefore looking for a macro that will activate if the [c1].value changes even if no-one clicks in that cell.

    In an ideal world this macro should work when people are away from their desks and look something like

    Sub Worksheet_Realtime()

    if trigger_cell.value > trigger_level
    call send_alert_email()
    end if

    End Sub

    My current workaround is to use application.ontime and set my time lag to ~1 minute but this seems much more intensive than it needs to be. It would start to slow down old machines if there were 3 or 4 background sheets firing macros every 15s or so - not to mention the interference with other macros! It seems like the sort of problem others would have come across before - but I have yet to find a solution in my websearching! (though the question has been asked here before: Automatically execute an excel macro on a cell changed by a real-time add-in - Stack Overflow)


  2. #2
    MrExcel MVP VoG's Avatar
    Join Date
    Jun 2002

    Default Re: VBA Run macro on cell value change from formula

    Try like this

    Private Sub Worksheet_Calculate()
    Static oldval
    If Range("C1").Value <> oldval Then
        oldval = Range("C1").Value
        'rest of your code here
    End If
    End Sub
    HTH, Peter
    Please test any code on a copy of your workbook.

  3. #3

    Default Re: VBA Run macro on cell value change from formula

    That seems to work - doesn't seem to slow down much either even with several ifs checking different conditions!

    I'll let you know how it fares with the bloomberg terminal - inactive workbooks may become my enemy...

  4. #4

    Default Re: VBA Run macro on cell value change from formula

    That works perfectly - even with bloomberg running on a background sheet.
    Thanks for the tip

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts