unable to add row to Excel 2007 TABLE

[Wakazula]

Hello.

I have recently started exploring the Excel 2007 "table" feature and
have discovered that you cannot add a new row to a locked worksheet.

OBJECTIVE
- using Excel 2007, enable user to add rows to table by pressing the
tab key
- using Excel 2007 security: prevent a user from modifying a formula
contained within a table cell

STEPS TO REPRODUCE
a. create a new Excel 2007 work sheet
b. enter the following data such that colum B *basically* mirrors
column A
A1: TitleA
A2: hello
A3: world
B1: =A2
B2: =A3
c. highlight cells A1 to B3
d. Insert -> Table -> OK
e. now... if you go to B4 and press TAB, Excel will automatically
create an new row

(so far so good)

f. Ctrl+A (to highlight all cells)
g. right click: format cells
h. protection tab
i. uncheck "Locked" (so it is now unlocked)
j. click the ColumnB header/button to select the entire ColumnB
g. right click: format cells
h. protection tab
i. check "Locked" (so it is now locked)
j. click "review" tab -> protect sheet -> select all available options
k. now... if you to B4 and press TAB, Excel WILL NOT automatically
create an new row

Is it possible to set the permissions in such a way that the add new
row feature (see e. above) will still work?

Any insight you can provide would be greatly appreciated.

 

[CellShocked]

Hello.

I have recently started exploring the Excel 2007 "table" feature and
have discovered that you cannot add a new row to a locked worksheet.

OBJECTIVE
- using Excel 2007, enable user to add rows to table by pressing the
tab key
- using Excel 2007 security: prevent a user from modifying a formula
contained within a table cell

STEPS TO REPRODUCE
a. create a new Excel 2007 work sheet
b. enter the following data such that colum B *basically* mirrors
column A
A1: TitleA
A2: hello
A3: world
B1: =A2
B2: =A3
c. highlight cells A1 to B3
d. Insert -> Table -> OK
e. now... if you go to B4 and press TAB, Excel will automatically
create an new row

(so far so good)

f. Ctrl+A (to highlight all cells)
g. right click: format cells
h. protection tab
i. uncheck "Locked" (so it is now unlocked)
j. click the ColumnB header/button to select the entire ColumnB
g. right click: format cells
h. protection tab
i. check "Locked" (so it is now locked)
j. click "review" tab -> protect sheet -> select all available options
k. now... if you to B4 and press TAB, Excel WILL NOT automatically
create an new row

Is it possible to set the permissions in such a way that the add new
row feature (see e. above) will still work?

Any insight you can provide would be greatly appreciated.

Use some logic. The answer is no, IF the data is locked.

So, you must develop a routine where you unlock it, add the row, and
then relock it, if you still have whatever security concern you had
before with whomever it is you are worried about adding rows to unlocked
tables.

 

[Wakazula]

@CellShocked

Thank you for taking the time to reply to my inquiry.

My primary objective here is to:

take advantage of all the features offered by "Excel 2007 Tables"
AND
protect a formula in one column from being edited

I thought this would be fairly straight forward to do, but have been
unable to make it work.

 

[Wakazula]

It seems others have had at the same problem:
http://www.sqldrill.com/excel/miscellaneous-excel-subjects/1024627-protecting-formulas-tables.html


Here are a few proposed work arounds:

1. basically do nothing: don't try to protect forumlas in an Excel
table
2. populate the table with a number of blank rows
3. write a macro that automatically manages security for you

SOURCE CODE BY Roger Govier

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