Run code when cell value changes

  • Is there a way to monitor(watch) cell value changes w/o Watch window? Office2013. I have cell in witch i input raw data like let's say number 12345, i need formula or VBA sub to watch value changes and display me in other cell, date when was change occured, or if it's possible, days passed from change. Example: 12345 number was entered 20.5.2016., need to display days passed from 20.5.(18 days).
    Thanks in advance. Thank you for your time and love for Excel!


    P.s And if it's possible, non-volatile way. Thanks



    Also posted:http://answers.microsoft.com/e…b4-47df-bdee-049b13b008db

  • Re: Watch cell?


    B.W.B,


    In order to accomplish what you want you should put this code in the worksheet module and create a new worksheet called 'Changes'. It will create a record of changes to the cell you want to track (in this case A1):


    Example of resulting Values:


    [TABLE="width: 315"]

    [tr]


    [td]

    Previous Value

    [/td]


    [td]

    New Value

    [/td]


    [td]

    Date Changed

    [/td]


    [/tr]


    [tr]


    [td]

    MyFirstVal

    [/td]


    [td]

    MyNewVal

    [/td]


    [TD="align: right"]6/10/16 11:57 PM[/TD]

    [/tr]


    [tr]


    [td]

    MyNewVal

    [/td]


    [td]

    NewerValue

    [/td]


    [TD="align: right"]6/10/16 11:58 PM[/TD]

    [/tr]


    [tr]


    [td]

    NewerValue

    [/td]


    [td]

    GetThePicture

    [/td]


    [TD="align: right"]6/11/16 12:01 AM[/TD]

    [/tr]


    [/TABLE]

    Matt Mickle
    Using Excel 2010,2013 & 2016

  • Re: Watch cell?


    No. I don't want list of changes. I wrote formula in B1 which referenecing to itself. NOW()-B1 , which is probably the error. I need to display (days passed)value in B1 when A1 value change, NOW() minus previous value from B1, if it's possible in dd-hh:mm format. Thank you for your time!


    In Sheet1

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
    
    
        If Not Intersect(Target, Me.Range("A1")) Is Nothing Then MacroM
        
    End Sub


    Module:

    Code
    Sub MacroM()
    
    
        Range("B1").Select
        ActiveCell.FormulaR1C1 = "=(NOW()-RC[0])"
    
    
    
    
    End Sub
  • Re: Watch cell?


    B.W.B.,


    Try this:


    1.format cell B1 dd-hh:mm
    2.Create a Custom Document Property named "PreviousTimeVal"
    Go To File > Properties (In BackStage View) > Advanced Properties > Custom > Create Your Property (Store as the Number Type you can type in a number like today's date 42532) > Add


    Now paste this code to your worksheet module:


    Matt Mickle
    Using Excel 2010,2013 & 2016

  • Re: Watch cell?


    Sorry for upper case letters, IT IS WORKING, it has to be in Sheet1, not Module1.
    Now, how to look A1:A2 and put date&time in B2 on Sheet2 ??
    It has to change A1 or A2 value or both values, to trigger code.

  • Re: Watch cell?


    Maybe try this instead:


    Matt Mickle
    Using Excel 2010,2013 & 2016

  • Re: Watch cell?


    Yes, this is it! THANK YOU ALL!


    But, acctually, i now realise that this is not what i wanted :( My bad.
    I need, every time when, for example, i run Sort Macro, inside that Sort macro to insert code "how long has it been from last entry in A1:A2"
    That Sort code is in Module1.



    P.s. I need more codes, do they have be on same Sheet? Codes inside code.


    [ATTACH=CONFIG]69403[/ATTACH]

  • Re: Watch cell?


    Quote

    I need, every time when, for example, i run Sort Macro, inside that Sort macro to insert code "how long has it been from last entry in A1:A2"


    This is the line that defines the amount of time that has passed between now and the last time an entry has been made in A1 or A2:


    Code
    wsTwo.Range("B1") = Now() - ThisWorkbook.CustomDocumentProperties("PreviousTimeVal").Value

    Matt Mickle
    Using Excel 2010,2013 & 2016

  • Re: Watch cell?


    No. Not how many days(hhmm) passed between entries. Need to insert volatile code(now()) inside Sort Macro to trigger that "how long" code to show me, every time Sort macro is used, days passed from last entry in A1A2... Or maybe, i don't follow(understand)?
    And that "how long" code need to be updated when i insert values in A1A2 B1B2...., and to show me days(hhmm) passed from entry, when i trigger Sort Macro.
    Sorry for bad english, i'm just a redneck!

  • Re: Watch cell?


    Maybe i'm asking difficult question?
    I may have a problem. When i insert eg. 145cm, and after few days enter 140cm, and after another few+ days enter 145cm again, i get same days that was in first 145 value. :(

  • Re: Watch cell?


    Quote from mrmmickle1;772526

    This is the line that defines the amount of time that has passed between now and the last time an entry has been made in A1 or A2:


    Code
    wsTwo.Range("B1") = Now() - ThisWorkbook.CustomDocumentProperties("PreviousTimeVal").Value


    It has to "refresh" cells containning values even they did't changed, something like this:


    Code
    [Range("B8").Select
                ActiveCell.FormulaR1C1 = ActiveCell.Value
                Range("B9").Select
                ActiveCell.FormulaR1C1 = ActiveCell.Value
                Range("D8").Select
                ActiveCell.FormulaR1C1 = ActiveCell.Value
                Range("D9").Select
                ActiveCell.FormulaR1C1 = ActiveCell.Value


    :drunk:

  • Re: Watch cell?


    Not sure I understand entirely what you're trying to do but I think if you just add these few lines of code to the bottom of your sort macro and leave the event change code as is...it may give you what you want...


    Code
    'Add these to declarations
        Dim ws As Worksheet
        Dim wsTwo As Worksheet
         
        'Add these to the bottom of your code.  After sort is performed....
        Set ws = Sheets("Sheet1")
        Set wsTwo = Sheets("Sheet2")
        wsTwo.Range("B1") = Now() - ThisWorkbook.CustomDocumentProperties("PreviousTimeVal").Value
        ThisWorkbook.CustomDocumentProperties("PreviousTimeVal") = Now()


    Quote

    It has to "refresh" cells containning values even they did't changed, something like this:

    If you're using change event code this occurs like this:


    Code
    Range("B9")= Target.Value



    As I mentioned in my first post: I would recommend tracking the changes in a log.


    You can track when A1 or A2 changes as well as when the Sort Macro is used. The log does not have to be visible it can be hidden....

    Matt Mickle
    Using Excel 2010,2013 & 2016

  • Re: Watch cell?


    Thank you so much! Couple of questions if i may? So, my Sort Macro is in Module1, "how long" code is on Sheet1 ChangeEvent. Can you guide or help write code, line of code in Sort Macro to trigger Sheet1 code, and put some On Error just in case? And, please, i need formatting inside code, for cells that will contain info of cell value change, to be like FORMAT(dd&"days"hh&"hours"&:mm&"mnts"). Summary, on Sheet1 there are numerous monitored cells A1A2, B1B2, C1C2, E1E2, A5A6, D5D6..., on Sheet2 are displaying cells A1, B1, C1, E1, A5, D5..., on sheet2 is also Sort form button which triggers Sort macro. Usually, I enter height of fuel level in reservoir and temperature(A1A2) on sheet1. On sheet2 are sorted heights in centimetres and cells that will contain info of last measurement. Thanks a ton!

  • Re: Watch cell?


    I'm still a little confused on what your doing. To activate the change event code on "Sheet1" you have to physically change something on the worksheet.



    So if you want to run the change code you will need to do something like this....


    Code
    'Sheet 1 code module
    Private Sub Worksheet_Change(ByVal Target As Range)
        MsgBox "I have been activated by code in module 1"
    End Sub



    Module 1 Code:

    Code
    Sub Test()
    
    
    'Your Sort Code Here
        
        Sheets("Sheet1").Range("Z1") = "Trigger" 'Changing a cell on Sheet1 will activate the change event code....
        
    End Sub


    If you don't want to change a value on Sheet1, there are many different types of events that you can use...i.e. Selection Change, Activate, DeActivate etc.... in addition you can also change the event code to run a macro from it like this:

    Code
    Private Sub Worksheet_Change(ByVal Target As Range)
         Call MyMacroNameHere
    End Sub


    For formatting times you'll need to convert the times into different segments. Days, Hours & Minutes Then use a series of if statements to determine how to handle the plural... See this thread for reference: http://www.ozgrid.com/forum/showthread.php?t=25985


    you can conditionally format your cells to highlight different cells based on different criteria

    Matt Mickle
    Using Excel 2010,2013 & 2016

  • Re: Watch cell?


    But, can we call change event code from module1 Sort Macro? I posted Sort macro code previously on first page of this tread. "How long" code is apparently fine, still breaking my head, how to do incode format for cells.

  • Re: Watch cell?


    Quote

    But, can we call change event code from module1 Sort Macro?


    So if you want to run the change code you will need to do something like this....


    Code
    'Sheet 1 code module
    PrivateSub Worksheet_Change(ByVal Target As Range) 
        MsgBox "I have been activated by code in module 1" 
    End Sub


    Module 1 Code:

    Code
    Sub Test() 
         
       'Your Sort Code Here <---------------------------########**************############
         
        Sheets("Sheet1").Range("Z1") = "Trigger" 'Changing a cell on Sheet1 will activate the change event code....
         
    End Sub

    Matt Mickle
    Using Excel 2010,2013 & 2016

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!