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]

My take on that:
1) I think you want to check B3.Formula, not B3.Value
2) I would use a Select Case block rather than what you have, but
3) I think if a checkbox is checked, you don't want it to appear in the formula? If so, it makes more sense to loop over the checkboxes and build the formula. That would be a lot shorter and easier to maintain. If there are other cb's that are not part of this, you need a way to ID those that are involved in the formula. For that I would use the cbox Tag property to ID them.
4) You should state what type of controls you're using. There are ActiveX controls and there are Forms controls. I don't think a Forms checkbox has a Tag property.
5) Please use code tags (vba button on posting toolbar) to maintain indentation and readability of your code

 

[msmith012776]

I am using Forms checkboxes I have a lot of cb's in my sheet. I dont understand what you mean by loop over.

 

[msmith012776]

Something to this effect?

Private Sub CheckBox20_Click()

If B3.Formula = "=SUM(E7,E8,E9,E10,E11,E12,E13)" Then
    B3.Formula = "=SUM(E8,E9,E10,E11,E12,E13)"
End If

End Sub

 

[Micron]

"Looping over" usually means running code that somehow makes use of a group of controls, columns, rows, etc. and examines them one at a time. If you only want checked boxes to be in the formula, then code would loop over that collection of checkboxes, and examine whether or not they are checked and if so, add its name to a string and build a formula. So if cb's 1, 3 and 5 were checked you'd end up with B3.Formula = "=B1+B3+B5". That may not be accurate for your needs but that is how it would work. You might have about 15 lines of code instead of 115 or whatever it is you have there. ANY combination would generate the correct formula, whether that is only one checkbox, all of them or somewhere in between.

You didn't reveal how many can be chosen at a time (or what the limitations might be wrt choices), but let's go with 3. Out of 12 checkboxes where 3 at a time are chosen, there are 220 combinations. You can use a cell Combin function to check this out.

 

[Micron]

I see that you mentioned that there are six, so your max number of combinations would be 20? I know I would not want to code for 20 possible combinations if I could let a loop build it for me. Also, change your mind and add one more and then what? The number jumps to 35 and you need 15 more tests, whereas a loop would need no modification.

 

[msmith012776]

So I have 7 total boxes that will be considered in this expression. Basically, B3 is a SUM of E7:E13. I have cb's @ Range I7:I13. When selecting cb I7 I want to remove E7 from the formula in B3.

I see what you are saying I will need to reevaluate how I'm trying to accomplish this task.

 

[Micron]

If you get stuck I would need to know if you are familiar with using the immediate window in the vb editor.
I guess I had it backwards (you're removing clicked controls, not adding them) but I think the best approach is still to loop over the seven controls (not 6 as I thought). I'd have to think about how to "subtract" rather than add and that will take one or two shots of Jameson.

Or you could flip the logic and only use checked ones, unless those would always be in the majority. Doing that would make the user choose 5 or 6 that they want instead of the usual 1 or 2 that they don't. Perhaps then, start with an array of the 7 control names and remove the checked ones from the array. Have never done that but seems doable. Then use the array to build the string.

 

[msmith012776]

So pic below may provide a bit of overview of what I'm trying to accomplish. The Paid cb strikes out the amount due in E7:E13. Thats working like a charm. The cb's in I7:I13 are for when an item is cleared thru the bank and I want to remove the amount due (E column) from B3. Right now I have to manually remove them from B3 just trying to automate this a bit. C3 thru E3 are other groups of payments. Working on one thing at a time. I figure once this is figured out then I should be able to apply to the others fairly easily.

 

[msmith012776]

I want to thank you Micron for assisting me with the thinking process to accomplish my task. I ended up wiping out my thought process since I felt like I was being a bit stubborn with how I was trying to accomplish the task. I sat here for the last hour or so just thinking about how to do what I want. Here's what I came up with.

So in J7:J13 I used the IF function "=IF(I7=TRUE,0,E7)" and copied to the other cells, then I used =SUM(J7:J13) in B3. Works flawlessly.

I will just make the text in J7:J13 white to hide the results. Also this will expand to more fields if needed with little intervention. I think I was way overthinking what I was trying to do and got lost in the sauce. LOL. KISS = Keep it simple stupid.

Thank you again.