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

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.

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



jkendrick75 wrote:
>
> 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.

--

Dave Peterson

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.


"Dave Peterson" wrote:

> 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
>
>
>
> jkendrick75 wrote:
> >
> > 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.
>
> --
>
> Dave Peterson
>

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



jkendrick75 wrote:
>
> 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.
>
> "Dave Peterson" wrote:
>
> > 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
> >
> >
> >
> > jkendrick75 wrote:
> > >
> > > 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.
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson

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.

"Dave Peterson" wrote:

> You shouldn't be running the MstrCBXClick macro yourself. It's run when you
> click the checkbox itself.
>
>
>
> jkendrick75 wrote:
> >
> > 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.
> >
> > "Dave Peterson" wrote:
> >
> > > 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
> > >
> > >
> > >
> > > jkendrick75 wrote:
> > > >
> > > > 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.
> > >
> > > --
> > >
> > > Dave Peterson
> > >
>
> --
>
> Dave Peterson
>