Automatically updates the sum whenever i update quantity

[abdul hayee]

Hi Guys
I am doing some excel work using macros.
Here i am stuck at a point. There is a button name "Add Row" in it. whenever we click this button one row is added in the sheet.
I want to perform SUM operation at columns for example column "M" starting from M8 to Last added Row. It is working fine as long as i click the Add Row button it add the numbers in that column and show the result in a cell.
But this formula is defined in a function "Add Row" and always called when this is clicked.
I want to know where else i can define this formula so that it automatically sum whenever i change quantity of M columns or row is inserted.
Below is the code. Hope i am able to describe my point

Please Login or Register  to view this content.

query_excel_1.png

Thanks and best regards

[TMS]

Not entirely sure why you are doing the sum inside the VBA routine. Why not just drop the SUM formula into the last row? Then the formula would recalculate when you make changes. Every time you add a row, the routine would still overwrite the formula, but that wouldn't strictly be necessary.

Alternatively you could use a worksheet change event handler to monitor the column and update the total.

For me though, just use a formula. It's not complicated so there's no overhead.

[abdul hayee]

Not entirely sure why you are doing the sum inside the VBA routine. Why not just drop the SUM formula into the last row? Then the formula would recalculate when you make changes. Every time you add a row, the routine would still overwrite the formula, but that wouldn't strictly be necessary.

Alternatively you could use a worksheet change event handler to monitor the column and update the total.

For me though, just use a formula. It's not complicated so there's no overhead.

Dear i want to do this because after automated i will give this file to different members for updating their data and for them it will be difficult for them to update sum everytime they add a row.
In the example above i am just using one column for simplicity, in real world there are more than one columns as you can see in the image file and also these columns should be increase.

can you tell me event change handler routine? how it works and how to define this routine?

Thanks

[TMS]

Please post a sample workbook.