I don’t know if you need all this background to answer my question, but it
couldn’t hurt.

I am working in Excel 2002. Currently I have a table (A1:U14), 13 rows of
data, row 14 is the Total row. There are 2 columns (columns F and J) that
have formulas which calculate 3 columns preceding them (column F’s formula is
=SUM(C5:E5), column J’s formula is =SUM(G5:I5). These 2 columns with the
formulas are locked cells. The Total row has formulas which calculate the
figures in each column, the formula is =SUM(C5:C13), the cells in this row
are also locked.

I have unlocked all the cells that I want the user to have access to and
have protected the sheet.

Rows can be inserted into this table, by the user with this macro which is
assigned to a command button:

Sub AddRow()

Dim Msg, Style, Title, Response
Dim rngAdd As Range
Set rngAdd = ActiveCell

Msg = "Do you want to insert a row?" 'Define message.
Style = vbYesNo + vbDefaultButton2 'Define buttons.
Title = "Insert a Row" 'Define MsgBox title.

Response = MsgBox(Msg, Style, Title)

If Response = vbYes Then 'User chose Yes.

ActiveSheet.Unprotect

rngAdd.Offset(1, 0).EntireRow.Insert
rngAdd.EntireRow.Copy

Set rngAdd = rngAdd.Offset(1, 0)

rngAdd.PasteSpecial xlPasteFormats
rngAdd.PasteSpecial xlPasteFormulas
rngAdd.EntireRow.SpecialCells(xlCellTypeConstants).ClearContents

ActiveSheet.Protect

End If

End Sub

When figures are entered into the cells of this new row, the formulas in the
Total row do not recognize that a row has been added. When I unprotect the
sheet, the formula in the Total row is =SUM(C5:C13). The formula should
change to =SUM(C5:C14) with the addition of the new row.

I’m assuming it is not updating due to the cells being locked. How can I
have the Total row recognize a new row has been added, yet keep the user from
enter information into the cells of the Total row by mistake?

--
Jamie