Check box questions (2)

[prophetUK]

Hi, sorry if this has already been answered somewhere before but
trawled through and couldn't find anything.

I am using check boxes to perform calculations based on TRUE or FALSE

1. How can I get the cell link in the Format Control to update if
copy and paste several rows with several check boxes further down th
sheet?
Ie. one check box links to C3 but I want to copy that whole row dow
one and have the check box autoupdate its link to C4.

2. How can I get a sum of multiple IF results from my check boxes?
For example:
=(IF(A1=TRUE,50,0))+(IF(B1=TRUE,50,0))
So that if both check boxes were ticked the total value would be 100
And if either were ticked but not both the value would be 50, etc.

Thanks

 

[Dave Peterson]

I think you have to assign it yourself.

But you could use a macro to populate the worksheet with a bunch of checkboxes
(I used the checkbox from the Forms toolbar):

Option Explicit
Sub addCBX()
Dim myCBX As CheckBox
Dim myCell As Range

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

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

I used the cell under the checkbox as the linked cell. But I gave it a custom
format of ;;;. You can see the value in the formula bar, but it looks empty
when you look at the worksheet.