Tip: Other languages are Google-Translated. You can visit the English version of this link.
Log in
x
or
x
x
Register
x

or

How to keep table expandable by inserting table row in a protected worksheet in Excel?

The auto expanding function of table will be lost after protecting the worksheet in Excel. For example, there is a table named Table1 in your protected worksheet, when you type anything under the last row, the table will not automatically expand to include the new row. Is there method to keep table expandable by inserting new row in a protected worksheet? The method in this article can help you to achieve it.

Keep table expandable by inserting table row in a protected worksheet with VBA code


arrow blue right bubble Keep table expandable by inserting table row in a protected worksheet with VBA code


As below screenshot shown, a table named Table1 in your worksheet, and the last column of the table is a formula column. Now you need to protect the worksheet to prevent the formula column from changing, but allow to expand the table by inserting new row and assign new data into the new cells. Please do as follows.

1. Click Developer > Insert > Button (Form Control) to insert a Form Control button into your worksheet.

2. In the popping up Assign Macro dialog box, click the New button.

3. In the Microsoft Visual Basic for Applications window, please copy and paste the below VBA code between the Sub and End Sub paragraphs in the Code window.

VBA code: Keep table expandable by inserting table row in a protected worksheet

Dim pswStr As String
'Update by ExtendOffice 20181106
    pswStr = "123"
    On Error Resume Next
   Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:=pswStr
       ActiveSheet.Range("A1").Select
        Range("Table1[[#Headers],[Total]]").Select
        Selection.End(xlDown).Select
        Selection.Offset(1, 0).Select
        ActiveCell.FormulaR1C1 = "new"
        ActiveSheet.Protect Password:=pswStr, DrawingObjects:=False, _
                        Contents:=True, Scenarios:=False, _
                        AllowFormattingCells:=True, AllowFormattingColumns:=True, _
                        AllowFormattingRows:=True, AllowInsertingColumns:=True, _
                        AllowInsertingRows:=True, AllowInsertingHyperlinks:=True, _
                        AllowDeletingColumns:=True, AllowDeletingRows:=True, _
                        AllowSorting:=True, AllowFiltering:=True, _
                        AllowUsingPivotTables:=True
    Selection.ClearContents
    Application.ScreenUpdating = True

Notes:

1). In the code, number “123” is the password you will use to protect the worksheet.

2). Please change the table name and the name of the column that is containing formula you will protect.

3). And change the offset in the code Selection.Offset(1, -4).Select to the first target column of the table. For example, if there are six columns in the table, please change the Offset to (1, -5).

4. Press the Alt + Q keys to close the Microsoft Visual Basic for Applications window.

5. Select the cells in the table you need to assign new data into except the formula column, then press the Ctrl + 1 keys to open the Format Cells dialog box. In the Format Cells dialog box, uncheck the Locked box, and then click the OK button. See screenshot:

6. Now protect your worksheet with password you have specified in the VBA code.

From now on, after clicking the Form Control button in your protected worksheet, the table will be expandable by inserting new row as below screenshot shown.

Note: you can modify the table except the formula column in the protected worksheet.


arrow blue right bubbleRelated articles:


Recommended Productivity Tools

Ribbon of Excel (with Kutools for Excel installed)

300+ Advanced Features Increase Your Productivity by 71%, and Help You To Stand Out From Crowd!

Would you like to complete your daily work quickly and perfectly? Kutools For Excel brings 300+ cool and powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) for 1500+ work scenarios, helps you solve 82% Excel problems.

  •  Deal with all complicated tasks in seconds, help to enhance your work ability, get success from the fierce competition, and never worry about being fired.
  •  Save a lot of work time, leave much time for you to love and care the family and enjoy a comfortable life now.
  •  Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands, and give you a healthy body.
  •  Become an Excel expert in 3 minutes, and get admiring glance from your colleagues or friends.
  •  No longer need to remember any painful formulas and VBA codes, have a relaxing and pleasant mind, give you a thrill you've never had before.
  •  Spend only $39, but worth than $4000 training of others. Being used by 110,000 elites and 300+ well-known companies.
  •  60-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years. Buy once, use forever.
  •  Change the way you work now, and give you a better life immediately!

Office Tab Brings Efficient And Handy Tabs to Office (include Excel), Just Like Chrome, Firefox, And New IE

  • Increases your productivity by 50% when viewing and editing multiple documents.
  • Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
  • Open and create documents in new tabs of same window, rather than in new windows.
  • Help you work faster and easily stand out from the crowd! One second to switch between dozens of open documents!
Say something here...
symbols left.
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.
  • To post as a guest, your comment is unpublished.
    Mac · 2 months ago
    Try this Vba code for add new line in you table

    Sub Tab_Line_Add()
    Dim pswStr As String
    pswStr = "123"
    On Error Resume Next
    Application.ScreenUpdating = False
    ActiveSheet.Unprotect Password:=pswStr
    ActiveSheet.Range("D8").Select
    'D8 is tabel header
    Range("Table1[[#Headers],[Total]]").Select
    Selection.End(xlDown).Select
    Selection.ListObject.ListRows.Add AlwaysInsert:=False
    ActiveSheet.Protect Password:=pswStr

    End Sub
    .
  • To post as a guest, your comment is unpublished.
    Brandi · 7 months ago
    So I tried this however it adds a new row at the bottom of the workbook on line 1048576 however my table only has about 800 records. I have no idea why it's doing this!
    • To post as a guest, your comment is unpublished.
      crystal · 6 months ago
      Hi Brindi,
      The code has been updated with the problem solved, please have a try and thank you for your comment.
      • To post as a guest, your comment is unpublished.
        Ali · 4 months ago
        Hi Crystal, theproblem is same. I created a new table for myself with only 2 rows. Once I click the button the list is expanded till end of table without adding any Rows. It should be adding to Row number 3.
      • To post as a guest, your comment is unpublished.
        Ali · 4 months ago
        Hi Crystal, theproblem is same. I created a new table for myself with only 2 rows. Once I click the button the list is expanded till end of table without adding any Rows. It should be adding to Row number 3.