Consulting

Results 1 to 8 of 8

Thread: Trigger Macro by change in formula value within a cell

  1. #1

    Trigger Macro by change in formula value within a cell

    I wish to trigger a macro by a change in a formula value within a particular cell. I am totally green in this area, however searching through the internet came across the following script which partly solved my problem:

    Dim O18Val
    Private Sub Worksheet_Activate()
    O18Val = [O18]
    End Sub
    Private Sub Worksheet_Calculate()
    If [O18] <> O18Val Then
    MsgBox "XXXXXXXX"
    Application.EnableEvents = False
    'run code here
    Application.EnableEvents = True
    O18Val = [O18]
    End If
    End Sub

    The above script gives the message "XXXXXXXX" when the formula value of cell O18 changes.

    Now I wish to extend the above script for other cells as well.
    E.g. If formula value of cell O19 changes, a message "YYYYYYYYY" pops up.

    Any ideas please? Thanks

  2. #2
    Hello paulmer,

    I think this should work for you


    [VBA]Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Set Target = ActiveSheet.Range("O18")
    If Target.Value <> "" Then
    MsgBox "Cell O18 has change!"
    End If
    End Sub
    [/VBA]

    Andy

  3. #3
    Hi Andy,

    Thanks for your response, however it does not work as I expect it cause every change in any cell triggers the macro.

    The script I posted in orginal message works great. I only need to be able to have a different message when the formula value of another cell changes.

    I mean that when the value of O18 changes, I get the message "XXXXXXX", when the value of O19 changes I get the message "YYYYYYYY", when the value of O20 changes I get the message "ZZZZZZ", etc.

  4. #4
    VBAX Mentor
    Joined
    Jun 2004
    Posts
    363
    Location
    Sorry - I missed the part about it being a formula.... The code I've submitted is for the change of the formula, not the formula's result.
    [vba]Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("O18")) Is Nothing Then
    MsgBox "O18 has changed"
    End If
    If Not Intersect(Target, Range("O19")) Is Nothing Then
    MsgBox "O19 has changed"
    End If
    End Sub[/vba]
    Last edited by mbarron; 04-01-2010 at 01:50 PM. Reason: Didn't read the post correctly

  5. #5
    Moderator VBAX Sage SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    7,814
    Location
    [VBA]
    Private Sub Worksheet_Change(ByVal Target As Range)
    Select Case Target
    Case Range("O18")
    Message = "XXX"
    Case Range("O19")
    Message = "YYY"
    Case Else
    Message = "ZZZ"
    End Select

    Msgbox(Message)
    [/VBA]

  6. #6
    I have to trigger a macro whenever a cell value is updated from an online database. None of the above code works for me though, when i expected it should have. Can anyone tell me what should I change in above code?

  7. #7
    Administrator
    VP-Knowledge Base
    VBAX Grand Master mdmackillop's Avatar
    Joined
    May 2004
    Location
    Scotland
    Posts
    14,489
    Location
    Have a look at this recent thread
    MVP (Excel 2008-2010)

    Post a workbook with sample data and layout if you want a quicker solution.


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  8. #8
    Thanks alot ! Its working.

Posting Permissions

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