Using check boxes to include/exclude data in calculations

[jdunnisher]

I have several columns of numbers being used in various calculations on a
spreadsheet. I would like to place a check box at the top of each column and
be able to select or de-select the checkbox to determine whether or not the
numbers in that particular column are included in the calculations. Can
anyone assist me with a macro that would accomplish this?

 

[pogiman via OfficeKB.com]

Try using the checkbox control in the Forms menu or a validation since this
single reference serve as an ON/OFF switch for a whole column and an IF
function might help.

 

[Dave Peterson]

I'd use the checkbox from the Forms toolbar (not the checkbox from the Control
toolbox toolbar), just because these are easier to work with.

Show that Forms toolbar
Put a checkbox in each of the cells (row 1, columns A:G (for example))
Assign the linked cell to each of those checkboxes to the cell that the checkbox
is in (A1:G1 in my example).
Give those cells a custom format
Format|cells|number tab|Custom category:
Enter: ;;;
(3 semi-colons)
This will hide the true/false's in the worksheet, but you'll be able to see them
in the formulabar.

Now instead of using:
=sum(a2:G2)
you can use:
=sumif($a$1:$g$1,true,$a2:$g2)

If you want a macro to put those checkboxes in the cells, you can start with
this:

Option Explicit
Sub testme()

Dim myCBX As CheckBox
Dim myCell As Range

With ActiveSheet
.CheckBoxes.Delete 'nice for testing
For Each myCell In ActiveSheet.Range("a1:g1").Cells
With myCell
Set myCBX = .Parent.CheckBoxes.Add _
(Top:=.Top, Width:=.Width, _
Left:=.Left, Height:=.Height)
With myCBX
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
End With
.NumberFormat = ";;;"
End With
Next myCell
End With
End Sub


Adjust the range to what you need.

 

[jdunnisher]

Thanks for your help, Dave. I think you have me on the right track.

The values in each of these columns are used in dozens of formulas
throughout the entire workbook. Rather than modify each formula
individually, I'd like Excel to "ignore" the values for a particular column
whenever the checkbox is FALSE and "include" the values when the checkbox is
TRUE.

By default, all of the check boxes would be set to TRUE; I just want to be
able to de-select a check box and have those values excluded from
calculations throughout the entire workbook. Is this possible?

 

[Dave Peterson]

You can add another line to the macro:

With myCBX
.Value = xlOn '<-- added
.LinkedCell = myCell.Address(external:=True)
.Caption = ""
.Name = "CBX_" & myCell.Address(0, 0)
End With

But I think you're going to have to adjust the formulas.

 

[jdunnisher]

I was able to find a suitable resolution using the information in your first
reply. I appreciate your help.

 

[Christop]

Thanks Dave!!

I have been trying to put a check box in a sheet and then protect the sheet,
but leave the box where it can be changed. I used your macro to put the box
in the cell rather than on top and it works now.

You are the best. You have helped me with quite a lot of information that
you don't even know about, so I thought I would say thanks on this one.

 

[Dave Peterson]

Glad you got something that works!

Thanks Dave!!

I have been trying to put a check box in a sheet and then protect the sheet,
but leave the box where it can be changed. I used your macro to put the box
in the cell rather than on top and it works now.

You are the best. You have helped me with quite a lot of information that
you don't even know about, so I thought I would say thanks on this one.