need help using a checkbox to determine if a cell gets summed

J

jkendrick75

need help using a checkbox to determine if a cell gets summed. i have
designed a worksheet with items in one column, and how much the item is in
another column. what i want to do is to check a box next to an item and have
the cost of that item added to the other items that are checked. if the item
is unchecked, then resum the items that are checked. i would also like to
add an additional check box that will automatically check or uncheck all of
the other checkboxes. any help will be appreciated.
 
D

Dave Peterson

I put my items in B2:B20 and the costs in c2:c20.

Then I ran this macro to add the checkboxes. (The second macro does the check
all/uncheck all stuff--it's the checkbox in A1.)

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

With ActiveSheet
.CheckBoxes.Delete 'nice for testing

Set myRng = .Range("a1:a" & .Cells(.Rows.Count, "B").End(xlUp).Row)

For Each myCell In myRng.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)
If myCell.Address = myRng.Cells(1).Address Then
.OnAction = "MstrCBXClick"
End If
End With
.NumberFormat = ";;;"
End With
Next myCell
End With
End Sub

Sub MstrCBXClick()
Dim myCBX As CheckBox
Dim MstrCBX As CheckBox

Set MstrCBX = ActiveSheet.CheckBoxes(Application.Caller)

For Each myCBX In ActiveSheet.CheckBoxes
myCBX.Value = MstrCBX.Value
Next myCBX

End Sub

Then I put this formula in another cell:

=SUMIF(A2:A20,TRUE,C2:C20)

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

jkendrick75

i added the code to a module like the website suggested, i can get it to
create a check box in A1 by running , by running the macro 'addCBX' but that
is it. when i try to run the macro 'MstrCBXClick', i am getting an error of
"Run-time error '1004':
Unable to get the checkboxes property of the worksheet class"
and the line "Set MstrCBX = ActiveSheet.CheckBoxes(Application.Caller)" in
the
'Sub MstrCBXClick()' sub is highlighted. not sure where to go from here.
am i supposed to create the checkboxes manually, if so, in what column? do i
name them anything specific? thanks for looking into this.
 
D

Dave Peterson

You shouldn't be running the MstrCBXClick macro yourself. It's run when you
click the checkbox itself.
 
J

jkendrick75

ok, i found my problem, i wasn't reading your post close enough i guess.
when i read it i thought it said to put the items in a2 to a20, instead of b2
to b20. made the change and it works great. thanks.
 
Top