check box formula

[Perry Diels]

Hello,

I would like to place check boxes in a column that are linked to a given
formula. So far I managed to insert the check box, but I'm stuck on the
formula, I could not find the needed information in the Excel help. What I
want looks simple:

When clicking (enabling) the check box I want the sum of another cell to be
added to a target cell. I'll try to clarify with an example.

Cell D40 is the target, when the check box is checked I want to have the sum
of B2 added to the target (D40)
The Target can already contain a number, or it is empty. If another check
box is checked the sum of yet another cell should be added to the target
sum.

Another example:

I have three check boxes (A, B, C); The target is D40

A=sumB3 (=10)
B=sumB4 (=50)
C=sumB5 (=100)

No check boxes checked D40 = 0
If A is checked D40 = 10
If B is checked D40 = 50
If A & B checked together D40 = 60
If A & B & C are all checked D40 = 160

I hope this is understandable enough.

All help appreciated.
Perry

 

[J.E. McGimpsey]

One way:

Using checkboxes from the Forms toolbar, link each checkbox to a
corresponding cell in, say, column J, or amother empty column (e.g.,
for checkbox A, choose format control/control tab and enter J1 as
the Cell Link). You can then hide that column.

Then enter

D40: =SUMIF(J1:J3, TRUE, B1:B3)

 

[Perry Diels]

Hello J.E, thanks a lot for your answer.

Thanks to your help I'm already a lot further. For the time being I have
another question.

Is it possible to link two checkboxes in order that only one of them can be
checked at the same time. In other words, if let's say chack box A1 is yet
checked, when checking A2, checkbox A1 is automatically unchecked (and
vice-versa)

Best Regards,
Perry

 

[J.E. McGimpsey]

That's usually the role of option buttons, which you can also find
on the forms toolbar. With option buttons, however, you can only
assign a group (either all of them in the sheet, or those within a
group box) to a single cell, which will take on the index of the
selected option button (e.g., 1,2,3...) rather than the True/False
of checkboxes.

However, you can use a macro to do this:

Public Sub CheckBox_Click()
With ActiveSheet
If Application.Caller = "Check Box 1" Then
.CheckBoxes("Check Box 2") = _
IIf(.CheckBoxes("Check Box 1") = xlOn, xlOff, xlOn)
Else
.CheckBoxes("Check Box 1") = _
IIf(.CheckBoxes("Check Box 2") = xlOn, xlOff, xlOn)
End If
End With

Attach this macro to both Check Box 1 and Check Box 2 (adjust the
names as desired) - then checking one will uncheck the other and
vice versa and each will change the value of its own linked cell.

 

[Perry Diels]

Hello J.E. Thanks again,

So far it is working partly. Now when unchecking let's say check box 7,
check box 6 is automatically checked and vice versa.
I mean, it is one or the other, but always one of the two check boxes ar
checked.
What I want is simply prevent that both check boxes are checked
simultaneaously, but with the option to have none checked as well.

You see what I mean?

Kind Regards,
Perry

 

[J.E. McGimpsey]

Try this modification:

Public Sub CheckBox_Click()
With ActiveSheet
If Application.Caller = "Check Box 1" Then
With .CheckBoxes("Check Box 2")
If .Value = xlOn Then .Value = xlOff
End With
Else '.Caller = "Check Box 2"
With .CheckBoxes("Check Box 1")
If .Value = xlOn Then .Value = xlOff
End With
End If
End With
End Sub

 

[Perry Diels]

J.E. thanks again you're great!
I'm almost done with this Excel project, it works nice so far.

Now that Macro's are enabled, Excel complains about missing digital
signature that when opening that Workbook (see below FYI). The security
level is set to high and I prefer to keep it like that. I have tried signing
the workbook myself with the selfcert.exe tool, but it looks like this has
no influence on VB Macro's. Is there a way to have the Macro's working and
leaving the security level to high?

And one last question, is it possible to include a 'reset' method (button or
whatver) that resets ALL the checked check boxes in that Workbook (or
sheet). In other words, they should all default to the non-checked state.

Best Regards,
Perry

FYI: Macro's in this workbook are disabled because the security level is set
to high and the macros have not been digitally signed etc....

 

[J.E. McGimpsey]

One way:

Public Sub ClearCheckBoxes()
Dim ckBx As CheckBox
For Each ckBx In ActiveSheet.CheckBoxes
ckBx.Value = xlOff
Next ckBx
End Sub

 

[Perry Diels]

Dear J.E.

Thanks a lot for all your help, my project came to a good end.
During this task I have learned that the kind of information I needed is not
included with the Excel help files.

Thanks for the good job on this news group.

Best Regards,
Perry

----------------------------------------

One way:

Public Sub ClearCheckBoxes()
Dim ckBx As CheckBox
For Each ckBx In ActiveSheet.CheckBoxes
ckBx.Value = xlOff
Next ckBx
End Sub

state.