adding numbers macro

[arciduca79]

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

 

[Serkan]

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.

 

[arciduca79]

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!!

 

[Bob Phillips]

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)

 

[JE McGimpsey]

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 here:

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

 

[arciduca79]

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

 

[arciduca79]

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.

 

[Bob Phillips]

The totals is a workbook name. Select the cell, then go to
Insert>Names>Define 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]

Seriously JE

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