Formulas & Checkboxes

  • Thread starter Thread starter JesseP
  • Start date Start date
J

JesseP

I am trying to create a formula that says whenever checkboxes in
columns H2:H200 are checked to add one and divide by 2 in another
cell. Any suggestions?
 
If you use linked cells for each of the checkboxes, you can use =countif() to
see how many are true. (I used the underlying cell as the linked cell in my
fromulas.)

And do you mean any checkbox in H2:H200?

=if(countif(h2:h200,True)>0,(A1+1)/2,"whatgoes here")

or do you mean for each checkbox in H2:H200?
=if(h2=true,....)

And I'm not sure what to add one to and what to divide by 2????
 
How do you link Cells? Yes, any checkbox within H2:h200 that is
checked. I want to add the checked boxes to like I2 and then divide
by 2 in the same box.
 
What kind of checkbox?

A checkbox from the Forms toolbar:
rightclick and select "Format Control"
then Control tab
then assign the linked cell

A checkbox from the ControlToolbox toolbar:
Show the controltoolbox toolbar
go into design mode (first icon on that toolbar)
rightclick on the checkbox
select properties
look for Linked cell and enter the address there.

Since you have 199 of them:

You can run a macro (or two):

Option Explicit
Sub controlToolboxCheckbox()

Dim OLEObj As OLEObject

For Each OLEObj In ActiveSheet.OLEObjects
If TypeOf OLEObj.Object Is msforms.CheckBox Then
OLEObj.LinkedCell = OLEObj.TopLeftCell.Address(external:=True)
OLEObj.TopLeftCell.NumberFormat = ";;;"
End If
Next OLEObj

End Sub

Sub FormsCheckbox()

Dim myCBX As CheckBox

For Each myCBX In ActiveSheet.CheckBoxes
myCBX.LinkedCell = myCBX.TopLeftCell.Address(external:=True)
myCBX.TopLeftCell.NumberFormat = ";;;"
Next myCBX

End Sub

The numberformat = ";;;" will make it appear that the cell is empty.

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

But save your work first. This uses the topleftcell of the checkbox. You'll
want to verify that the checkboxes are within the cell you want.
 
Back
Top