Protecting Formulas in Tables

H

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?
 
R

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
 
H

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top