I dont know if you need all this background to answer my question, but it
couldnt 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 Fs formula is
=SUM(C5:E5), column Js 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.

Im 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