Question regarding Checkboxes changing formulas in cells?

[msmith012776]

I have a spreadsheet that I use to keep track of payments made. I have a cell(B3) that contains the formula "=E7+E8+E9+E10+E11+E12+E13". Curious if I can use a checkbox to when checked change the formula to, let say for example "=E8+E9+E10+E11+E12+E13" to remove an entry from the formula.

I tried this when clicked.

If B3 = "=E7+E8+E9+E10+E11+E12+E13" Then 'Nothing checked yet.
B3 , "=E8+E9+E10+E11+E12+E13", ""

I get no errors but nothing happens??

Whole code below sorry for the length there is 6 checkboxes that will need to be removed and checked against? Maybe I'm going about this the wrong way. Just trying to reduce human error. Code is trying to catch all scenarios of check box combinations. Everything below the If was my original attempt which did error out. The IF statement I've been messing with trying different things.

Thank you for your time and suggestions.

Private Sub CheckBox20_(Click)
If B3 = "=E7+E8+E9+E10+E11+E12+E13" Then 'Nothing checked yet.
B3 , "=E8+E9+E10+E11+E12+E13", ""

ElseIf B3.Value = "=E7+E9+E10+E11+E12+E13" Then 'E8 checked only.
B3.Value = "=E9+E10+E11+E12+E13"

ElseIf B3.Value = "=E7+E8+E10+E11+E12+E13" Then 'E9 checked only.
B3.Value = "=E8+E10+E11+E12+E13"

ElseIf B3.Value = "=E7+E8+E9+E11+E12+E13" Then 'E10 Checked Only.
B3.Value = "=E8+E9+E11+E12+E13"

ElseIf B3.Value = "=E7+E8+E9+E10+E12+E13" Then 'E11 Checked Only.
B3.Value = "=E8+E9+E10+E12+E13"

ElseIf B3.Value = "=E7+E8+E9+E10+E11+E13" Then 'E12 Checked Only.
B3.Value = "=E8+E9+E10+E11+E13"

ElseIf B3.Value = "=E7+E8+E9+E10+E11+E12" Then 'E13 Checked Only.
B3.Value = "=E8+E9+E10+E11+E12"
'************************************************************************
ElseIf B3.Value = "=E7+E10+E11+E12+E13" Then ' E8 & E9 Checked.
B3.Value = "=E10+E11+E12+E13"

ElseIf B3.Value = "=E7+E9+E11+E12+E13" Then ' E8 & E10 Checked.
B3.Value = "=E9+E11+E12+E13"

ElseIf B3.Value = "=E7+E9+E10+E12+E13" Then ' E8 & E11 Checked.
B3.Value = "=E9+E10+E12+E13"

ElseIf B3.Value = "=E7+E9+E10+E11+E13" Then ' E8 & E12 Checked.
B3.Value = "=E9+E10+E11+E13"

ElseIf B3.Value = "=E7+E9+E10+E11+E12" Then ' E8 & E13 Checked.
B3.Value = "=E9+E10+E11+E12"

ElseIf B3.Value = "=E7+E8+E11+E12+E13" Then ' E9 & E10 Checked.
B3.Value = "=E8+E11+E12+E13"

ElseIf B3.Value = "=E7+E8+E10+E12+E13" Then ' E9 & E11 Checked.
B3.Value = "=E8+E10+E12+E13"

ElseIf B3.Value = "=E7+E8+E10+E11+E13" Then ' E9 & E12 Checked.
B3.Value = "=E8+E10+E11+E13"

ElseIf B3.Value = "=E7+E8+E10+E11+E12" Then ' E9 & E13 Checked.
B3.Value = "=E8+E10+E11+E12"

ElseIf B3.Value = "=E7+E8+E9+E12+E13" Then ' E10 & E11 Checked.
B3.Value = "=E8+E9+E12+E13"

ElseIf B3.Value = "=E7+E8+E9+E11+E13" Then ' E10 & E12 Checked.
B3.Value = "=E8+E9+E11+E13"

ElseIf B3.Value = "=E7+E8+E9+E11+E12" Then ' E10 & E13 Checked.
B3.Value = "=E8+E9+E11+E12"

ElseIf B3.Value = "=E7+E8+E9+E10+E13" Then ' E11 & E12 Checked.
B3.Value = "=E8+E9+E10+E13"

ElseIf B3.Value = "=E7+E8+E9+E10+E12" Then ' E11 & E13 Checked.
B3.Value = "=E8+E9+E10+E12"

ElseIf B3.Value = "=E7+E8+E9+E10+E11" Then ' E12 & E13 Checked.
B3.Value = "=E8+E9+E10+E11"
'************************************************************************
ElseIf B3.Value = "=E7+E11+E12+E13" Then 'E8 & E9 & E10 Checked.
B3.Value = "=E11+E12+E13"

ElseIf B3.Value = "=E7+E10+E12+E13" Then 'E8 & E9 & E11 Checked.
B3.Value = "=E10+E12+E13"

ElseIf B3.Value = "=E7+E10+E11+E13" Then 'E8 & E9 & E12 Checked.
B3.Value = "=E10+E11+E13"

ElseIf B3.Value = "=E7+E10+E11+E12" Then 'E8 & E9 & E13 Checked.
B3.Value = "=E10+E11+E12"
'************************************************************************
ElseIf B3.Value = "=E7+E09+E12+E13" Then 'E8 & E10 & E11 Checked.
B3.Value = "=E9+E12+E13"

ElseIf B3.Value = "=E7+E09+E11+E13" Then 'E8 & E10 & E12 Checked.
B3.Value = "=E9+E11+E13"

ElseIf B3.Value = "=E7+E09+E11+E12" Then 'E8 & E10 & E13 Checked.
B3.Value = "=E9+E11+E12"
'************************************************************************
ElseIf B3.Value = "=E7+E09+E10+E13" Then 'E8 & E11 & E12 Checked.
B3.Value = "=E9+E10+E13"

ElseIf B3.Value = "=E7+E09+E10+E12" Then 'E8 & E11 & E13 Checked.
B3.Value = "=E9+E10+E12"
'************************************************************************
ElseIf B3.Value = "=E7+E12+E13" Then 'E8 & E9 & E10 & E11 Checked.
B3.Value = "=E12+E13"

ElseIf B3.Value = "=E7+E11+E13" Then 'E8 & E9 & E10 & E12 Checked.
B3.Value = "=E11+E13"

ElseIf B3.Value = "=E7+E11+E12" Then 'E8 & E9 & E10 & E13 Checked.
B3.Value = "=E11+E12"
'************************************************************************
ElseIf B3.Value = "=E7+E8" Then 'E9 & E10 & E11 & E12 & E13 Checked.
B3.Value = "=E8"

ElseIf B3.Value = "=E7+E9" Then 'E8 & E10 & E11 & E12 & E13 Checked.
B3.Value = "=E9"

ElseIf B3.Value = "=E7+E10" Then 'E8 & E9 & E11 & E12 & E13 Checked.
B3.Value = "=E10"

ElseIf B3.Value = "=E7+E11" Then 'E8 & E9 & E10 & E12 & E13 Checked.
B3.Value = "=E11"

ElseIf B3.Value = "=E7+E12" Then 'E8 & E9 & E10 & E11 & E13 Checked.
B3.Value = "=E12"

ElseIf B3.Value = "=E7+E13" Then 'E8 & E9 & E10 & E11 & E12 Checked.
B3.Value = "=E13"
'************************************************************************
Else
B3.Value = "=0"

End If
End Sub

 

[Micron]

Well, I'm glad I was able to impart some kind of benefit to the outcome. It seems to me that you arrived at a simpler solution than where I thought this might go, and that's a bonus. I have to admit that I'm not completely digesting your solution, but after all it's late here now and I'm out of steam!

 

[msmith012776]

So basically the statement in the J column states that if the I Column (the cb's) is True then result is 0 if its false then its the amount shown in the E column. Then I used SUM in B3 to add the J column up. This way I don't have to change the formula in B3 ever.

 

[Micron]

Got it, thanks. I guess that's why they call it a helper column.