vendredi 1 mars 2019

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

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

enter image description here

Image 2: Check Boxes Added

enter image description here

Image 3: One Check Box Checked

enter image description here

Image 4: 2 Checkboxes Checked

enter image description here




Aucun commentaire:

Enregistrer un commentaire