Excel Hints

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


    VB:
    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 
    
    
    Formatting tags added by mark007
    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
    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.
    VB:
    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 
    
    
    Formatting tags added by mark007
    Last edited by mbarron; 04-01-2010 at 01:50 PM. Reason: Didn't read the post correctly

  5. #5
    Moderator VBAX Master SamT's Avatar
    Joined
    Oct 2006
    Location
    Near Columbia
    Posts
    2,020
    Location
    VB:
     
    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) 
    
    
    Formatting tags added by mark007

  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
    12,503
    Location
    Have a look at this recent thread
    MVP (Excel 2008-2010)

    "Provide sample data and layout if you want a quicker solution." - MD


    To help indent your macros try Smart Indent

    Please remember to mark threads 'Solved'

  8. #8

Posting Permissions

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