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
Aucun commentaire:
Enregistrer un commentaire