Tracking changes in values and posting in a series of cells

Question

I have two cells with each returning a total from a series independent from each other. I would like to post these totals in new cells each time a change is made to either of the cells, with the values of both cells recorded each time. This is a score sheet that would track a running score showing that score each time there is a change to either of the cells.

Example:

Your example isn't much of an example. It doesn't show what your output should look like. Can you modify your question to say when `Cell1` does this, then `Cell2` does this, as it stands right now your question is somewhat vague. — K.Dᴀᴠɪs, Oct 18 '17 at 16:48
Your right-I wasnt clear. I have two cells that capture separate sum totals, AR67 and CU67 in my sheet as an example . When either of those totals change I would like to post the values for both cells in a new sheet that tracks every change. When the value of AR67 changes I would like to post that new value PLUS the value of CU67-even though it did not change. My intent is to create a running score tracker-so I hope this clarifies the question.Thanks in advance for your help! — Curt Nichols, Oct 19 '17 at 15:43
Ran out of time....Your right-I wasnt clear. I have two cells that capture separate sum totals, AR67 and CU67 in my sheet as an example . When either of those totals change I would like to post the values for both cells in a new sheet that tracks every change. So when the value of AR67 changes I would like to post that new value PLUS the value of CU67-even though it did not change. So AR67 would post to sheet 2 cell B1 and CU67 would post to sheet to cell B2 and when either AR67 or CU67 changes they would post to C1 and C2 etc, Hope that helps! Thanks for taking time to respond! — Curt Nichols, Oct 19 '17 at 15:55
Were you wanting to Change both of `Sheet2` values when a single change is made in either column on sheet1, or update row `A` sheet2 when a change has been placed in col `AY`, and row `B` on sheet2 when a cell has changed in col `DB`? — K.Dᴀᴠɪs, Oct 20 '17 at 20:54
Change both of Sheet2 values when a single change is made in either column on sheet1-as long as those changes post in sheet two as you previously were able to achieve. — Curt Nichols, Oct 20 '17 at 21:01

K.Dᴀᴠɪs · Answer 1 · 2017-10-20T21:09:24.827

0

Important!

This needs to be pasted into Sheet1 object. You can get there by right-clicking the Sheet1 tab > View Code.

Also, I have done only minimal testing. Please let me know if this works for you.

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Debug.Print Now; " >> ws1 Value Chg'd @ "; Target.Address
    Dim ws As Worksheet, iSectAY As Range, iSectDB As Range
    Dim RngAY As Range, RngDB As Range
    Dim RngAR As Range, RngCU As Range

    Set ws = ThisWorkbook.Worksheets(1)
    Set RngAR = ws.Range("AR67")
    Set RngCU = ws.Range("CU67")
    Set RngAY = ws.Range("AY:AY")
    Set RngDB = ws.Range("DB:DB")

    'Determine if the cell changed is a target cell
    Set iSectAY = Intersect(Target, RngAY)
    Set iSectDB = Intersect(Target, RngDB)
    Dim iRow As Integer
    If Not iSectAY Is Nothing Then
        iRow = 1
        If RngCU = "" Then
            Application.EnableEvents = False
            RngCU = 0
            Application.EnableEvents = True
        End If
    ElseIf Not iSectDB Is Nothing Then
        iRow = 2
        If RngAR = "" Then
            Application.EnableEvents = False
            RngAR = 0
            Application.EnableEvents = True
        End If
    Else
        Exit Sub    'Cell updated was not the target
    End If

    'Set Sheet2's objects
    Dim ws2 As Worksheet, UpdateRng1 As Range, UpdateRng2 As Range, iCol As Long
    Set ws2 = ThisWorkbook.Worksheets(2)
    iCol = ws2.Cells(iRow, Columns.Count).End(xlToLeft).Column + 1
    Set UpdateRng1 = ws2.Cells(1, iCol)
    Set UpdateRng2 = ws2.Cells(2, iCol)

    UpdateRng1 = RngAR.Value
    UpdateRng2 = RngCU.Value

End Sub

edited Oct 20 '17 at 21:09

answered Oct 19 '17 at 19:53

K.Dᴀᴠɪs

9,384
11
31
39

Excellent work! The data sequences out exactly as I envisioned in sheet 2, but is dependent on physically entering data in the reference cells (AR67 and CU67) in sheet 1. Both of these cells in sheet one return a sum total from different series in sheet 1. So if I key in a value in sheet 1 AR67 or CU67 the change in value does record for both every time one or the other cell value changes in sheet 2. Hope that makes sense. – Curt Nichols Oct 20 '17 at 17:10
What is the range you want to trigger the event? Example: Do you want sheet 2 to update when you enter a value in any cell in column A or row 1? Do you want to trigger when any cell on sheet 1 is changed? – K.Dᴀᴠɪs Oct 20 '17 at 17:50
When sheet 1 AR57 or CU57 changes it needs to trigger the event. AR57=SUM(AY7+AY11+AY15+AY19+AY23+AY27+AY31+AY35+AY39+AY43+AY47+AY51+AY55+AY59+AY63) CU57=SUM(DB7+DB11+DB15+DB19+DB23+DB27+DB31+DB35+DB39+DB43+DB47+DB51+DB55+DB59+DB63) I have command buttons assigned that return cumulative sum total values to a target cell in each row and this is repeated for multiple rows in the sheet thats assigned for each player to track scoring.. AR-57 and CU-57 reflects the updated totals for the assigned range. Thanks again! – Curt Nichols Oct 20 '17 at 18:38
The only way this event fires is when the cell changes. Technically, the cells `AR57` and `CU57` never change, because the formula that you entered into it always stays the same. So I can make the event fire when a change occurs in either `AY` or `DB` columns. ///// Another method would be using the `Worksheet_Calculate` event, but this would require that you use two helper cells that keeps track of the before values so it can compare if the value changes after each time the worksheet recalculates, or you could possibly use `Global Variables`, but I'm not too fond of using those for your issue – K.Dᴀᴠɪs Oct 20 '17 at 20:09
KD, if the event fires when a change occurs in columns AY or DB should not create an issue or conflict. Thanks! – Curt Nichols Oct 20 '17 at 20:24
Updated. Please let me know if this is what you are looking for – K.Dᴀᴠɪs Oct 20 '17 at 21:09
KD, I used the ghost cell option and added a line to each command button to get the code to fire: – Curt Nichols Oct 23 '17 at 14:32

score 0 · Answer 2 · answered Oct 23 '17 at 14:42

I used the following in conjunction with the editing the command buttons to get the results needed

Private Sub CommandButton108_Click()
Range("CI7") = Range("CI7") + 2
Range("CA7") = Range("CA7") + 2
Range("DH7") = Range("DH7")
End Sub
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    Debug.Print Now; " >> ws1 Value Chg'd @ "; Target.Address
    Dim ws As Worksheet, iSectAY As Range, iSectDH As Range
    Dim RngAY As Range, RngDB As Range
    Dim RngAR As Range, RngCU As Range

    Set ws = ThisWorkbook.Worksheets(1)
    Set RngAR = ws.Range("AR67")
    Set RngCU = ws.Range("CU67")
    Set RngAY = ws.Range("AY7:AY63")
    Set RngDH = ws.Range("DH7:DH63")

    'Determine if the cell changed is a target cell
    Set iSectAY = Intersect(Target, RngAY)
    Set iSectDH = Intersect(Target, RngDH)
    Dim iRow As Integer
    If Not iSectAY Is Nothing Then
        iRow = 1
        If RngCU = "" Then
            Application.EnableEvents = False
            RngCU = 0
            Application.EnableEvents = True
        End If
    ElseIf Not iSectDH Is Nothing Then
        iRow = 2
        If RngAR = "" Then
            Application.EnableEvents = False
            RngAR = 0
            Application.EnableEvents = True
        End If
    Else
        Exit Sub    'Cell updated was not the target
    End If

    'Set Sheet4's objects
    Dim ws4 As Worksheet, UpdateRng1 As Range, UpdateRng2 As Range, iCol As Long
    Set ws4 = ThisWorkbook.Worksheets(4)
    iCol = ws4.Cells(iRow, Columns.Count).End(xlToLeft).Column + 1
    Set UpdateRng1 = ws4.Cells(1, iCol)
    Set UpdateRng2 = ws4.Cells(2, iCol)

    UpdateRng1 = RngAR.Value
    UpdateRng2 = RngCU.Value

End Sub

Tracking changes in values and posting in a series of cells

2 Answers2

Important!