Formula will not auto update when sheet is protected

I am working with a sheet that has a subtotal at the bottom of a column. When I want to add data, there is a macro that inserts a new row, but when I add the data, the cell that shows the total for the column does not update to include the new row.

For example, the formula in the subtotal column is this:

=SUBTOTAL(9,G7:G26)

When I insert a row, if the sheet is unprotected, the formula will update to show:

=SUBTOTAL(9,G7:G27)

When I insert a row, if the sheet is protected, the formula does not update.

If I unprotect the sheet, the formulas are likely to be tampered with, and if I only unlock that one cell, that one may be altered as well. Can I do anything that would allow the sheet to remain protected, but update the formula when new data is entered?

Also, any other suggestions for the layout or design of this sheet would be great. I'm still learning a lot about excel. Thanks for any help.

--One more thing, I did add validation to the cell as suggested in another thread, and left the cell unlocked, but discovered that if I hit the "Delete" key when the cell is selected, it clears the formula.

I have found that Excel does not update formulas if a row is inserted immediately above the formula row. Someone else might have a more elegant solution, but you could insert a blank row directly above the formulas, then insert your new row above that dummy row. You can reduce the size of the blank row and format its borders so it just looks like a divider.

BTW - lurve your buttons at the top!

Hi Jason,

You've probably resolved your problem by now but here's a few more ideas:

*Change the layout by:
1) moving your subtotal to the top of the column (this way it is always visible with the use of "freeze panes") & changing it to sum all the column below its own cell (see 2nd sheet in attached file).
2) locking the top 6 rows (this will include the new subtotal cell) & unlocking all rows below that. To lock/unlock the rows, unprotect the sheet, select the range, press [ctrl + 1], choose [Protection] & tick or untick [Locked], then reprotect the sheet.
If you do this it changes/eliminates the need for some of your coding so I have slightly changed the impact of the macros & saved new versions in the "SuggestedMacros" module.

*If you want your subtotal to remain at the bottom:
-use a "workbook open macro" to set the sheet protection to "user interface only" therefore the macro can still do what is required. (see the "this workbook module of the attached file)
-a dynamic named range within the sub total to allow for insertion or deletion of rows. (see "Wormy Red w named range" in file)

*Some people suggest that a good coding practice involves the use of unique names for macros & not using any words that are already specific keywords/methods/constants of VBA so I have changed "Save" to FileSave & Protect to SheetProtect.

hth
Rob

Thanks for the feedback, I'm pleased I could help :-)

Rob