Hi all,
I am very unexperienced so this may sound too easy for someone, but it
actually getting on my nerves... Here's the deal:
I'm working on an excel spreadsheet and I want to add a check box wit
a "double" macro that does (when selected) and "un-does" (when blank
an operation on a series of numbers.

Say I have these random numbers:

2 5 5
3 4 5
1 9 7

x y z

...where "x, y, z" are the blank cells where I'd like the result of th
operation to appear.

I want to add a checkbox on each line that, when clicked on, take
every number in the line and re-writes it in the "x" cell, making i
possible to sum it to the others. Say, writing something like "+A1" i
the "x" cell, "+B1" in the "y" cell and "+C1" in the "z" cell (wher
"A1" is the name of the cell where number 2 is, and so on).

So if I click on two checkboxes, I will have "+A1+A2" in the "x" cell
"+B1+B2" in the "y" cell and "+C1+C2" in the "z" cell.

Can anyone help me??? THANK YOU

--
Message posted from http://www.ExcelForum.com

I think you don't need a macro for that.
When you press and hold CTRL while selecting cells you
want added with the mouse, Excel shows their sum at the
bottom right corner of the window. You can right click
this section and change it to min, max, average, etc. as
well.
-----Original Message-----
Hi all,
I am very unexperienced so this may sound too easy for
someone, but it'
actually getting on my nerves... Here's the deal:
I'm working on an excel spreadsheet and I want to add a
check box with
a "double" macro that does (when selected) and "un-does"
(when blank)
an operation on a series of numbers.

Say I have these random numbers:

2 5 5
3 4 5
1 9 7

x y z

...where "x, y, z" are the blank cells where I'd like the
result of the
operation to appear.

I want to add a checkbox on each line that, when clicked
on, takes
every number in the line and re-writes it in the "x"
cell, making it
possible to sum it to the others. Say, writing something
like "+A1" in
the "x" cell, "+B1" in the "y" cell and "+C1" in the "z"
cell (where
"A1" is the name of the cell where number 2 is, and so
on).

So if I click on two checkboxes, I will have "+A1+A2" in
the "x" cell,
"+B1+B2" in the "y" cell and "+C1+C2" in the "z" cell.

Can anyone help me??? THANK YOU!


---
Message posted from http://www.ExcelForum.com/

.

Well, thanks for the suggestion, but I really DO need the macro: I nee
to be able to do and undo the sum with the CHECKBOX!!

--
Message posted from http://www.ExcelForum.com

Here's one way.

Firstly, in the first column of the results (x y z) cell, give that cell a
name of 'totals (without the apostrophes).

Then put this code into the worksheet code module. To generate/remove the
addition, select the appropriate cell in column D

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim oTotals As Range
Application.EnableEvents = False
On Error GoTo sub_exit
Set oTotals = Range("totals")
If Target.Column = 4 Then
With Target
If .Value = "a" Then
.Value = ""
oTotals.Offset(0, 0).Formula = Replace(oTotals.Offset(0,
0).Formula, "+" & .Offset(0, -3).Address(False, False), "")
oTotals.Offset(0, 1).Formula = Replace(oTotals.Offset(0,
1).Formula, "+" & .Offset(0, -2).Address(False, False), "")
oTotals.Offset(0, 2).Formula = Replace(oTotals.Offset(0,
2).Formula, "+" & .Offset(0, -1).Address(False, False), "")
If oTotals.Offset(0, 0).Formula = "=" Then
oTotals.Offset(0, 0).Formula = ""
oTotals.Offset(0, 1).Formula = ""
oTotals.Offset(0, 2).Formula = ""
End If
Else
.Value = "a"
.Font.Name = "Marlett"
If oTotals.Offset(0, 0).Formula = "" Then
oTotals.Offset(0, 0).Formula = "="
oTotals.Offset(0, 1).Formula = "="
oTotals.Offset(0, 2).Formula = "="
End If
oTotals.Offset(0, 0).Formula = oTotals.Offset(0, 0).Formula
& "+" & .Offset(0, -3).Address(False, False)
oTotals.Offset(0, 1).Formula = oTotals.Offset(0, 1).Formula
& "+" & .Offset(0, -2).Address(False, False)
oTotals.Offset(0, 2).Formula = oTotals.Offset(0, 2).Formula
& "+" & .Offset(0, -1).Address(False, False)
End If
.Offset(0, 1).Activate
End With
End If
sub_exit:
Application.EnableEvents = True
End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"arciduca79 " wrote in message
...
Hi all,
I am very unexperienced so this may sound too easy for someone, but it'
actually getting on my nerves... Here's the deal:
I'm working on an excel spreadsheet and I want to add a check box with
a "double" macro that does (when selected) and "un-does" (when blank)
an operation on a series of numbers.

Say I have these random numbers:

2 5 5
3 4 5
1 9 7

x y z

..where "x, y, z" are the blank cells where I'd like the result of the
operation to appear.

I want to add a checkbox on each line that, when clicked on, takes
every number in the line and re-writes it in the "x" cell, making it
possible to sum it to the others. Say, writing something like "+A1" in
the "x" cell, "+B1" in the "y" cell and "+C1" in the "z" cell (where
"A1" is the name of the cell where number 2 is, and so on).

So if I click on two checkboxes, I will have "+A1+A2" in the "x" cell,
"+B1+B2" in the "y" cell and "+C1+C2" in the "z" cell.

Can anyone help me??? THANK YOU!


---
Message posted from http://www.ExcelForum.com/

no need for a macro.

If the checkboxes are from the Forms toolbar, select each checkbox and
choose Format/Control. In the Control pane, enter an out of the way cell
in the Cell Link box, say, Z1 for thefirst row, Z2 for the second, and
Z3 for the third.

Then in A5:

=A1*($Z1=TRUE) + A2*($Z2=TRUE) + A3*($Z3=TRUE)

or, a bit more efficiently:

=SUMPRODUCT(B1:B3,--$Z1:$Z3)

You can then copy the formulae in A5 to B5 and C5.

You can download a sample workbook he

ftp://ftp.mcgimpsey.com/excel/arciduca79.xls


In article ,
arciduca79 wrote:

Hi all,
I am very unexperienced so this may sound too easy for someone, but it'
actually getting on my nerves... Here's the deal:
I'm working on an excel spreadsheet and I want to add a check box with
a "double" macro that does (when selected) and "un-does" (when blank)
an operation on a series of numbers.

Say I have these random numbers:

2 5 5
3 4 5
1 9 7

x y z

..where "x, y, z" are the blank cells where I'd like the result of the
operation to appear.

I want to add a checkbox on each line that, when clicked on, takes
every number in the line and re-writes it in the "x" cell, making it
possible to sum it to the others. Say, writing something like "+A1" in
the "x" cell, "+B1" in the "y" cell and "+C1" in the "z" cell (where
"A1" is the name of the cell where number 2 is, and so on).

So if I click on two checkboxes, I will have "+A1+A2" in the "x" cell,
"+B1+B2" in the "y" cell and "+C1+C2" in the "z" cell.

First of all, thanks a lot bob.
I am not able to make this work thoug. Let me walk trough what I a
doing.

1) I write the numbers in the cells
2) I write "totals" in the corresponding row
3) I open the visual basic editor and paste the code

It says error 449 and crashes...

Help!!!

Thanks a lot again Bob

--
Message posted from http://www.ExcelForum.com

Hey - you were absolutely right, no need for a macro. Thank you ver
much for replying so quickly and for the sample file.
Thanks dude.



--
Message posted from http://www.ExcelForum.com

Seriously JE

THANKS A LOT
!!!!!!!!!!!!!!!

--
Message posted from http://www.ExcelForum.com

The totals is a workbook name. Select the cell, then go to
InsertNamesDefine Name and add totals.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"arciduca79 " wrote in message
...
First of all, thanks a lot bob.
I am not able to make this work thoug. Let me walk trough what I am
doing.

1) I write the numbers in the cells
2) I write "totals" in the corresponding row
3) I open the visual basic editor and paste the code

It says error 449 and crashes...

Help!!!

Thanks a lot again Bob.


---
Message posted from http://www.ExcelForum.com/