How do I recalculate cell only if reference cell value changes?

[murph]

I have turned on the iterations (set to 1) for circular values.

here is the scenario:
I want to keep a runnng total of $$ used
I have a cummulative total cell (A1), and a used today (A2)
the formula in A1 is =A1+A2

this works well in the fact that it keeps the running total in A1. it
updates the total correctly when I put in a new value in A2.

The problem is that when any other cell value in the spreadsheet changes, it
recalucaltes A1 again and adds the value in A2 to the total again.
It also does this same recalculation on saving or opening the spreadsheet
which I also need to turn off somehow.

I would prefer not to have users to do a manual calculation if possible

 

[Per Jessen]

Hi

You could use an event macro to calculate the running total in A1 when a new
value is entered in A2.

Right click on the sheet tab, select "View Code". Paste the code below into
the code sheet and close the VBA editor.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$2" Then
If IsNumeric(Target.Value) Then
Range("A1") = Range("A1").Value + Range("A2").Value
End If
End If
End Sub

Hopes this helps.

 

[murph]

Thanks Per.
Worked very well until I saved and reopened the spreadsheet. I saved it as
a .xlsm spreadsheet so the vb macro would save.
When I reopened it, the code was still there, but not functional. If I
delete the macro, save it, add the macro back in it works again until
saved/closed/reopened.

Is there something I need to do when I save or open the spreadsheet to
enable the macro automatically?

-murph

 

[Per Jessen]

Murph,

I think you have to look at the security settings. Click the Office button >
Excel Options > Trust Center > Trust Center Settings > Macro Settings >
Enable all macros. > OK > Exit Excel and open your file.

Does it work as desired now ? I assume it does.

As this security setting maybe isn't desireable, you can create your own
digital signatur to sign you macro project. Do write again if you need help
whith this subject.

Regards,
Per

 

[murph]

This was just what I needed, Thanks Per

-murph

Murph,

I think you have to look at the security settings. Click the Office button >
Excel Options > Trust Center > Trust Center Settings > Macro Settings >
Enable all macros. > OK > Exit Excel and open your file.

Does it work as desired now ? I assume it does.

As this security setting maybe isn't desireable, you can create your own
digital signatur to sign you macro project. Do write again if you need help
whith this subject.

Regards,
Per