adding numbers in a single cell as a running total in that cell ?

Hello everyone, I am new to this forum and have been using excel for about 3 years. I have learned alot but still can not figure out how to make numbers add up in one box like a running total. Meaning, If I have a list and in the list is one cell for each item say like cell a1 is for a can of coke and cell a2 is for a car tire and so on,and i want to keep a running total by adding a number to cell a1 or a2 like say "3 i.e. 3 tires or cans of coke" and later that day i come back and need to add 2 more ......so i want to be able to just click on the cell a1 or a2 and enter the number 2 and the cell would add the number in it to the number im adding to it to show a result of 5 tires or whatever. I am sorry If i am not asking the question in an understandable manor but its the best way I can think to ask it.lol anyways, any help would be appreciated, Thanks in advance.


James

Hello James,

Welcome to the Forum!

This requires a few VBA worksheet event macros.

Please Login or Register  to view this content.

How to Save a Worksheet Event Macro
1. Copy the macro using CTRL+C keys.
2. Open your Workbook and Right Click on the Worksheet's Name Tab for the Worksheet the macro will run on.
3. Left Click on View Code in the pop up menu.
4. Paste the macro code using CTRL+V
5. Make any custom changes to the macro if needed at this time.
6. Save the macro in your Workbook using CTRL+S

That's only possible with VBA. This code needs to code on a sheet, it will watch what is entered into A1 and add to it each time you make a change.

Right-Click on Sheet name
Choose VIEW CODE
Paste in Code
Alt-F11 to close VBEditor
Save sheet

Please Login or Register  to view this content.

The colored part could be duplicated to set up a second cell to watch...perhaps A2. Just change the MyCell references to MyCell2 in the added section, and insert a Static MyCell2 reference at the top, too.

Play around with it.

Leith, what does the second formula do? I like yours way better than mine, so as I add your superior version to my .CLS options, I just want to make sure I fully understand it.

Thanks alot guys for the quick answers I really appreciate the help....I can grasp what your saying but im not sure where to make the changes in those macros. I have'nt had to use a macro for anything yet and know nothing about them. i am making a weekly list that includes 7 days and prices for paid line items each has a 25 cells in a colum and 1 colum for each day of the week. so the range of cells that need this macro to make it work for this sheet would be cells d3 to j3 all the way down to d28 to j28 and all of them inside that...... would i have to do that for every one of those boxes ? or would that macro work for the whole page? will I need to input header coordinates for each macro:? lol sorry.

Hello JB,

The Selection_Change() event records the cell's value before it is changed and saves it in the variable Prev. When the cell 's contents change, the Worksheet_Change() event fires. The current contents are then added to the previous value.

Hello Martin,

Thanks for sharing that. Interesting article on the approach. The Auto_Open method has been replaced by the Workbook_Open() event. Microsoft has not supported Auto_Open since the introduction of Excel 2000, and recommends users against using it.

The Auto_Open won't fire if the file is opened by automated means.