Put timestamp when a checkbox is ticked or unticked

Question

I have a worksheet with 3 rows and 7 columns (A1:G3).
A and B columns have 6 checkboxes (A1:B3). Boxes in columns A & B are linked to columns C & D respectively. Cells in columns E & F are just replicating columns C & D respectively (live E1 cell is =C1 and F3 cell is =D3).

I want to put a timestamp in cell G for each row when a checkbox is ticked or unticked by using Worksheet_Calculate event in VBA for that sheet.

My code works when used for just 1 row.

Private Sub Worksheet_calculate()
    Dim cbX1 As Range
    Set cbX1 = Range("A1:F1")
    If Not Intersect(cbX1, Range("A1:F1")) Is Nothing Then
        Range("G1").Value = Now()
    End If
End Sub

I want to combine the code for 3 rows.

Here are 2 variations:

1st one:

Private Sub Worksheet_calculate()
    Dim cbX1 As Range
    Dim cbX2 As Range
    Dim cbX3 As Range
    Set cbX1 = Range("A1:F1")
    Set cbX2 = Range("A2:F2")
    Set cbX3 = Range("A3:F2")
    If Not Intersect(cbX1, Range("A1:F1")) Is Nothing Then
        Range("G1").Value = Now()
    ElseIf Intersect(cbX2, Range("A2:F2")) Is Nothing Then
        Range("G2").Value = Now()
    ElseIf Intersect(cbX3, Range("A3:F3")) Is Nothing Then
        Range("G3").Value = Now()
    End If
End Sub

When I combine them with ElseIf like in the code above, a timestamp gets put in only G1, no matter if I tick B1 or C2.

2nd one:

Private Sub Worksheet_calculate()
    Dim cbX1 As Range
    Dim cbX2 As Range
    Dim cbX3 As Range
    Set cbX1 = Range("A1:F1")
    If Not Intersect(cbX1, Range("A1:F1")) Is Nothing Then
        Range("G1").Value = Now()
    End If
    Set cbX2 = Range("A2:F2")
    If Not Intersect(cbX2, Range("A2:F2")) Is Nothing Then
        Range("G2").Value = Now()
    End If
    Set cbX3 = Range("A3:F2")
    If Not Intersect(cbX3, Range("A3:F3")) Is Nothing Then
        Range("G3").Value = Now()
    End If
End Sub

When I combine them by ending each one with End If and start a new If, timestamp gets put in all of the G1, G2 and G3 cells, even if I tick just one of the boxes.

Worksheet Change or Worksheet Calculate? Also, `If Not Intersect(cbX1, Range("A1:F1")) Is Nothing` will always be true, since `cbX1` is the same as `Range("A1:F1")`, and similarly for the rest of the conditions. — BigBen, Dec 12 '18 at 06:41
@BigBen thanks for your quick reply. Worksheet Calculate since both - "the cells linked to checkboxes" and "the cells that replicate previous cells" are considered as a formula type. Worksheet change will only work if it's changed manually. Cells get TRUE and FALSE values based on formula. — M. Babayev, Dec 12 '18 at 06:56
Right, I was just commenting that you mention "Worksheet_Change" in your question.... — BigBen, Dec 12 '18 at 06:58
@BigBen oh, sorry, I think I just wrote my question too quick and made a mistake. Edited my question — M. Babayev, Dec 12 '18 at 07:00
Maybe see [this question](https://stackoverflow.com/questions/11406628/vba-code-doesnt-run-when-cell-is-changed-by-a-formula), as well as this [implementation](https://stackoverflow.com/questions/43184798/timestamp-when-a-cell-is-changed-via-a-formula-excel) of it. — BigBen, Dec 12 '18 at 07:04
@BigBen I've tried that solution, but for some reason I don't get timestamp update when I untick the checkbox. It gets updated only when I tick it — M. Babayev, Dec 12 '18 at 07:07

score 5 · Accepted Answer · 2018-12-12T07:52:37.747

You seem to be confusing Worksheet_Calculate with Worksheet_Change and using Intersect as if one of the arguments was Target (which Worksheet_Calculate does not have).

Intersect(cbX1, Range("A1:F1")) is always not nothing because you are comparing six apples to the same six apples. You might as well ask 'Is 1,2,3,4,5,6 the same as 1,2,3,4,5,6?'.

You need a method of recording the values of your range of formulas from one calculation cycle to the next. Some use a public variable declared outside the Worksheet_calculate sub procedure; personally I prefer a Static variant array declared within the Worksheet_calculate sub.

The problem with these is initial values but this can be accomplished since workbooks undergo a calculation cycle when opened. However, it is not going to register Now in column G the first time you run through a calculation cycle; you already have the workbook open when you paste in the code and it needs one calculation cycle to 'seed' the array containing the previous calculation cycle's values.

Option Explicit

Private Sub Worksheet_Calculate()
    Static vals As Variant

    If IsEmpty(vals) Then   'could also be IsArray(vals)
        vals = Range(Cells(1, "A"), Cells(3, "F")).Value2
    Else
        Dim i As Long, j As Long
        With Range(Cells(1, "A"), Cells(3, "F"))
            For i = LBound(vals, 1) To UBound(vals, 1)
                For j = LBound(vals, 2) To UBound(vals, 2)
                    If .Cells(i, j).Value2 <> vals(i, j) Then
                        Application.EnableEvents = False
                        .Cells(i, "G") = Now
                        Application.EnableEvents = True
                        vals(i, j) = .Cells(i, j).Value2
                    End If
                Next j
            Next i
        End With
    End If

End Sub

thank you very much for the answer. I not only works perfectly, but also opened my eyes to realize that I was on a wrong way. Good luck — M. Babayev, Dec 12 '18 at 07:16
Please note that I added disabling the events with Application.EnableEvents = False so that writing the timestamp did not trigger Worksheet_Change (if you had one of those). — , Dec 12 '18 at 07:24

Put timestamp when a checkbox is ticked or unticked

1 Answers1

Linked