Protecting Formulas in Tables

[Hugh]

In Excel 2007 I have a formatted table, 10 columns wide, in which 5 of the
columns have formulas.

There is a nice feature in Excel 2007 that allows me to type a value in a
cell which is directly beneath the table and the table then expands to
“consume†that value and automatically fills the remaining columns in the
table with, where appropriate, the formulas.

I want to protect the table so that the formulas are not inadvertently
overwritten AND take advantage of this feature.

However, if I lock and hide the cells, then Protect the worksheet, this
feature is disabled.

Can anyone help?

 

[Roger Govier]

Hi Hugh

The only way to do this is to resize your table first.
Click within table>Design tab>Resize>make the table as large as you are
likely to need.
Select the cells where you want to enter data>Format>Protection>remove
Locked.
Right click on sheet tab>Protect sheet.

The disadvantage of course is that if you are using the table as a
source for other things e.e Pivot Table, then the range will contain
lots of blank rows.

The only other way is via code.
The following event code was written for a Table called Table2
Data is entered in columns A and B, and columns C and D contain formulae.
You would need to amend to suit your situation.

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim tr As Long
tr = Target.Row
If Target.Count > 1 Then Exit Sub
If tr = 1 Then Exit Sub
If Target.Column <> 1 Then Exit Sub
ActiveSheet.Unprotect
ActiveSheet.ListObjects("Table2").Resize Range("$A$1:$D" & tr)
Range(Cells(tr + 1, "A"), Cells(tr + 1, "B")).Locked = False
ActiveSheet.Protect
End Sub

To install
Copy code as above
Right click on sheet tab>View Code
Paste code into white pane that appears
Alt+F11 to return to Excel

 

[Hugh]

Thanks for the help.

It's disappointing that these two functions won't work in harmony.

I've elected to leave the formulas unprotected and, if they get overwritten
they can be repaired.