Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    I have the following simple piece of code which updates a cell when the one adjacent changes - ie it keeps track of when updates are made:

    Private Sub Worksheet_Change(ByVal Target As Range)

    Application.EnableEvents = False 'Turn events off, or the changes progoate across the worksheet !

    If ActiveCell.Column = 2 Then 'Only apply the behaviour when the cell which changes is in Column 2
    If ActiveCell.Row > 6 Then Target.Offset(0, 1) = Int(Now()) 'Protects rows 1 to 6 from this behaviour
    End If

    Application.EnableEvents = True 'Turn events back on

    End Sub


    If any cell in Column 2 has its value changed, the corresonding cell in Column 3 has the date set to "now".

    This all works fine when type the new value, or (as is the normal case) the new value is entered by a macro.

    For various reasons, I have had to use formulae in the cells which can change, and the code no longer picks up the changes.

    So my question is this: how do I make Excel spot that a particular cell's value has changed and, if it has, update the date in the cell to its right ?

    Many thanks

    Martin

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The change event is triggered when the contents of the cell change, not the value. If the formula is not changed, but the value, then the contents of the cell have changed.

    What you will have to do is to not worry about the formulas (whose cells do not change) but the cells which do change and affect the formula. You either need to check each of the explicit cells which the formulae are dependent on, or check for changes in EVERY cell and look to see if they are precedents for the formulas in column 2. This 2nd method may be problematic if the precedent cells are on another worksheet as well as getting making data entry very sluggish as every change will have to be checked...

    Steve

  3. #3
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Hmmm . . monitoring the antecedent cells will be really tough as the specific source is the result of a complex lookup, and varies. I suppose I might be able do this when I work out how vlookup works in VBA !

    The values of the result cells do change and I'm still unsure why the event doesn't pick that up ?

  4. #4
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 Posts
    Just a thought: If there aren't too many references in a formula you could use a formula to pickup the latest {newest} date of any of it's references.

    So if A100 has the formula =A1*A7
    B100 would have the formula =Max(B1,B7)

    I hope this helps or at least gives you some ideas.

    Edit: Sorry the Max function should have referenced col. B.
    May the Forces of good computing be with you!

    RG

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

  5. #5
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thank you.

    I think I'm in a dead-end here - time to get innovative and think about it differently. Your idea gave me some more - I am going to re-write the whole funtionality !

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    The values of the result cells do change and I'm still unsure why the event doesn't pick that up ?
    As I mentioned, the change event triggers on the cell CONTENTS not the cell's VALUE. The value may change, but that is irrelevant, the content of the cell (the formula) has not changed and thus to excel, that cell has NOT changed. The cell that has changed (and that needs to be examined for a change) is the cell whose content did change.

    If you have a simple formula in the cell c1:
    =A1

    When A1 changes, the value in C1 changes, but the change is A1 and checking to see if C1 changed will not be noted since C1 did NOT change, it still has the formula =A1...

    Other than checking the cells that C1 is dependent on, the other option for looking at the values is to keep a table of all the values of the cells you are interested in and then whenever any cells are changed in the workbook, check the contents of all the cells of interest versus the saved table of past values and if the values have changed update the table and mark it as changed...

    Steve

  7. #7
    3 Star Lounger
    Join Date
    Feb 2003
    Location
    Runcorn, Cheshire, United Kingdom
    Posts
    372
    Thanks
    0
    Thanked 2 Times in 2 Posts
    Quote Originally Posted by MartinM View Post
    Thank you.

    I think I'm in a dead-end here - time to get innovative and think about it differently. Your idea gave me some more - I am going to re-write the whole funtionality !
    You could run a macro to snapshot the monitored cells to a hidden sheet - then use the code in column C to compare cells in B with the hidden values .

    Run the snapshot on opening the worksheet and have button to run the macro on demand.

  8. #8
    5 Star Lounger
    Join Date
    Jan 2004
    Location
    Praha
    Posts
    990
    Thanks
    56
    Thanked 105 Times in 90 Posts
    Thanks for all the ideas !

    Using a combination of them I have fixed the issue as follows FYI.

    Instead of doing a complex lookup on the sheet where data is displayed - the problem being as clearly stated, the value changes but the formula doesn't - I have put the lookup on the sheet which looked at by the vlookup command. Then VBA writes the results to the sheet where the data is displayed, and of course the "On change" macro spots it and writes the datestamp of when the change took place.

    Cheers everyone.

Posting Permissions

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