VBA - Add Cell Value to Total Sum if Checkbox is Checked

Question

I'm not sure if the heading is accurately describing what my query is, so I'll try my best to describe it here.

I have a sheet that keeps track of expenses and income and I have a macro that I use to insert check boxes into selected cells, link the checkbox to those cells and finally, apply a condition for a conditional format once the checkbox is checked and likewise if it is unchecked again.

Here is code that does that:

Sub:

Sub Insert_Checkbox_Link_Cell()

    Dim rngCel, myCells As Range
    Dim ChkBx As CheckBox
    Dim cBx As Long

    Set myCells = Selection

    myCells.NumberFormat = ";;;"

    Application.ScreenUpdating = False

    For Each rngCel In myCells

        With rngCel.MergeArea.Cells

            If .Resize(1, 1).Address = rngCel.Address Then

                Set ChkBx = ActiveSheet.CheckBoxes.Add(.Left, .Top, .Width, .Height)

                With ChkBx

                    .Value = xlOff
                    .LinkedCell = rngCel.MergeArea.Cells.Address
                    .Text = ""
                    .Width = 18
                    .Top = rngCel.Top + rngCel.Height / 2 - ChkBx.Height / 2
                    .Left = rngCel.Left + rngCel.Width / 2 - ChkBx.Width / 2
                    .Select

                    'Function Call
                    Selection.OnAction = "Change_Cell_Colour"

                End With

            End If

        End With

    Next rngCel

    If (Range(ChkBx.LinkedCell) = "True") Then

        myCells.Interior.ColorIndex = 43

    Else

        myCells.Interior.ColorIndex = 48

    End If

    Application.ScreenUpdating = True

End Sub

Function:

Function Change_Cell_Colour()

    Dim xChk As CheckBox
    Dim clickedCheckbox As String

    clickedCheckbox = Application.Caller

    Set xChk = ActiveSheet.CheckBoxes(clickedCheckbox)

    If xChk.Value = 1 Then

        ActiveSheet.Range(xChk.LinkedCell).Interior.ColorIndex = 43

    Else

        ActiveSheet.Range(xChk.LinkedCell).Interior.ColorIndex = 48

    End If

End Function

So how this works is, I select the range of cells I want to have the checkboxes in, then I run the macro and it inserts the checkboxes as stated above.

Now I am wanting to add a little more and I am not sure if it is possible.

In the image below, I have listed income and at the bottom is the total. So, as the money comes in, the checkbox is checked.

What I would like to do is this:

While the checkbox is UNCHECKED, I don't want the value in the cell to be added to the total count at the bottom.

When it is CHECKED, then the value in the cell should be added to the total count at the bottom.

Image 1: No Check Boxes

Image 2: Check Boxes Added

Image 3: One Check Box Checked

Image 4: 2 Checkboxes Checked

Answer 1

You could achieve this using Conditional Formatting and SUMIF formula to achieve this

I've used the following conditional formatting rules (You will need to change this for your ranges)

The conditional formatting is applied to both the cell fill and also the font text colour (to make the True/False be 'invisible')

In cell C6 (a merged range) I have the formula

=SUMIF($D$3:$D$5,TRUE,$C$3:$C$5)

Where cells in the D range contain the values of the linked cells for the checkboxes (i.e. True, False)and C range is the values you want to sum.

This is a much simpler approach then any VBA solution and personally, I'd remove the formatting of the cells from your vba above and just use the conditional formatting.

If you're looking for a VBA way to initiate this (except for the SUMIF formula) I've updated your below code to add the conditional formatting

Sub Insert_Checkbox_Link_Cell()
    Dim rngCel, myCells As Range
    Dim ChkBx As CheckBox
    Dim cBx As Long

    Set myCells = Selection
    myCells.NumberFormat = ";;;"

    Application.ScreenUpdating = False
    For Each rngCel In myCells
        With rngCel.MergeArea.Cells
            If .Resize(1, 1).Address = rngCel.Address Then
                Set ChkBx = ActiveSheet.CheckBoxes.Add(.Left, .Top, .Width, .Height)
                With ChkBx
                    .Value = xlOff
                    .LinkedCell = rngCel.MergeArea.Cells.Address
                    .Text = ""
                    .Width = 18
                    .Top = rngCel.Top + rngCel.Height / 2 - ChkBx.Height / 2
                    .Left = rngCel.Left + rngCel.Width / 2 - ChkBx.Width / 2
                End With
            End If
        End With
    Next rngCel

    With myCells
        ' Set default value
        .Value2 = False
        ' Add conditional formatting for False value
        With .FormatConditions
            .Add Type:=xlExpression, Formula1:="=" & myCells.Cells(1).Address(False, True) & "=False"
        End With
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 9868950
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
            With .Font
                .Color = -6908266
                .TintAndShade = 0
            End With
        End With
        ' Add conditional formatting for True value
        With .FormatConditions
            .Add Type:=xlExpression, Formula1:="=" & myCells.Cells(1).Address(False, True) & "=True"
        End With
        With .FormatConditions(.FormatConditions.Count)
            .SetFirstPriority
            With .Interior
                .Pattern = xlSolid
                .PatternColorIndex = xlAutomatic
                .Color = 52377
                .TintAndShade = 0
                .PatternTintAndShade = 0
            End With
            With .Font
                .Color = -16724839
                .TintAndShade = 0
            End With
        End With
    End With

    Application.ScreenUpdating = True
End Sub

Answer 2

You can give a value (eg: 1 for checked and 0 for unchecked) to the cell where the checkbox is added in your color change function. keep the cell's font color the same as the cell's fill color so that the value will be invisible to naked eyes. then in the total sum section, you can use sumif function.