Each time the check box is checked and linked cell D5 returns TRUE, what formula can I use to reduce the value in J5 by 1.

I tried using J1 as the begining value of say 10 but can't figure the formula in J5 to return 1 less each time D5 rings TRUE.

Tried a Select Case in a change event macro but the range/target AND the "TRUE"/value I could not get to work.

There will be several D cells with check box linked cells and the same amount of J values to be reduced as their respective D cell ring up a "TRUE".

Howard

Each time the check box is checked and linked cell D5 returns TRUE,
what formula can I use to reduce the value in J5 by 1.

I tried using J1 as the begining value of say 10 but can't figure the
formula in J5 to return 1 less each time D5 rings TRUE.

Tried a Select Case in a change event macro but the range/target AND
the "TRUE"/value I could not get to work.

There will be several D cells with check box linked cells and the
same amount of J values to be reduced as their respective D cell ring
up a "TRUE".

Howard

Did you try using a macro assigned to the checkboxes?

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Howard

Did you try using a macro assigned to the checkboxes?

--
Garry


Aha! I was able to make this work

Sub CheckBox2_Click()
If ThisWorkbook.Worksheets(1).Shapes("Check Box 2").OLEFormat.Object.Value = 1 Then
Range("J5").Value = Range("J5").Value - 1
End If
End Sub

Thanks for hint.

Howard

Howard

Did you try using a macro assigned to the checkboxes?

--
Garry


Aha! I was able to make this work

Sub CheckBox2_Click()
If ThisWorkbook.Worksheets(1).Shapes("Check Box
2").OLEFormat.Object.Value = 1 Then Range("J5").Value =
Range("J5").Value - 1 End If
End Sub

Thanks for hint.

Howard

Congrats, but that approach requires each control to have its own sub.
If you use an ActiveX control you can assign an index to its Tag
property. Then all can use the same routine. You can also assign a
GroupName to context-specific controls, for example, in different
sections of a form.

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

Congrats, but that approach requires each control to have its own sub.
If you use an ActiveX control you can assign an index to its Tag
property. Then all can use the same routine. You can also assign a
GroupName to context-specific controls, for example, in different
sections of a form.

--
Garry

Hmmm, don't know if it is a Form or an ActiveX. My testing is with a Forms of course.

I'll take a look at the ActiveX and see if I can 'ciffer what its Tag property is etc.

Howard

Oops.., forgot there's no Tag prop for this control!

--
Garry

Free usenet access at http://www.eternal-september.org
Classic VB Users Regroup!
comp.lang.basic.visual.misc
microsoft.public.vb.general.discussion

On Thursday, March 12, 2015 at 11:00:27 PM UTC-7, GS wrote:
Oops.., forgot there's no Tag prop for this control!

--
Garry

Just poking about, I put an activex check box on the sheet.

It came up numbered 22, don't know why as this is a new unused workbook...? Would have thought it would be 1.

With the code here, it errors out on this line:

Set cb = ActiveSheet.Shapes("Check Box 22")

With "Name not found"

Or with this as the name same as in the Property window, errors:

Set cb = ActiveSheet.Shapes("CheckBox22")

Does not support that Property or Method



Private Sub CheckBox22_Click()
Dim cb As Shape

Set cb = ActiveSheet.Shapes("Check Box 22")

If cb.OLEFormat.Object.Value = 1 Then
MsgBox "Checkbox is Checked"
Else
MsgBox "Checkbox is not Checked"
End If
End Sub

Now I remember why I use Forms.<g

Howard