fondauk
05-21-2012, 01:15 AM
Hi all,
I have a workbook that has a set of columns whereby a user will enter monthly estimated amounts, and a cell in the same row which totals those columns. The monthly cells are unprotected, all other cells on the worksheet are protected, but with no password
Using the Table Total rows, each column is then summed.
I have a macro that allows the user to add a row to the table when required.
All works fine in 2007, but one user has now moved to 2010 and the macro, or something else is changing the formula in the protected cell
The monthly cells are in columns N:AE, staring at row 4 downwards
The total formula is in column L, =SUM(N4:AE4)
When using in Excel 2010 and the "Add row" macro is run, the formula in the first row (row 4) is amended to =SUM(A4:AE5)
The first time a row is added, the formula in L6 is would be =SUM(N6:AE7)
New rows added will then sum the values in the new row and include the values in the table "Total row"
A manual workaround is to unprotect the worksheet and amend the formula in the first row, and formulas below will be updated....
The macro identifies the worksheet the "InsertRow" macro is called from, assigned the the name of the ListObject on the worksheet to a ListObject object and uses the .ListRows.Add code
Any ideas people?
I have a workbook that has a set of columns whereby a user will enter monthly estimated amounts, and a cell in the same row which totals those columns. The monthly cells are unprotected, all other cells on the worksheet are protected, but with no password
Using the Table Total rows, each column is then summed.
I have a macro that allows the user to add a row to the table when required.
All works fine in 2007, but one user has now moved to 2010 and the macro, or something else is changing the formula in the protected cell
The monthly cells are in columns N:AE, staring at row 4 downwards
The total formula is in column L, =SUM(N4:AE4)
When using in Excel 2010 and the "Add row" macro is run, the formula in the first row (row 4) is amended to =SUM(A4:AE5)
The first time a row is added, the formula in L6 is would be =SUM(N6:AE7)
New rows added will then sum the values in the new row and include the values in the table "Total row"
A manual workaround is to unprotect the worksheet and amend the formula in the first row, and formulas below will be updated....
The macro identifies the worksheet the "InsertRow" macro is called from, assigned the the name of the ListObject on the worksheet to a ListObject object and uses the .ListRows.Add code
Any ideas people?