How To Keep Table Expandable By Inserting Table Row In A Protected Worksheet In Excel

Hi, I am new to VBA and just starting to try teach myself and having little success :-(
I have a Table that has formulas in some of the columns, I need to be able to protect the worksheet as the worksheet will be used by those that may not be familiar with Excel. I need to be able to continually add rows and enter data, and for the table to expand.

The table is named "Data_Table" in tab "Data"
The headers are in the range A1:AC1, the last 6 columns are the ones with formulas in them, and are names as follows, "Branch response time", "CO response time", "ODG/MO response time", "Branch Due", "CO Due" & "ODG/MO due"

I tried the following code:VBA code.jpg

Any help will be appreciated. Thanks

When you are using a table the formulas should automaticly be copied down to the new rows when you insert a row

The following code will insert 1 row at the bottom

Please Login or Register  to view this content.

If there is only 1 table on your worksheet then you can leave the (1) behind listobjects, if you have multiple tables it might be easier to change the (1) to your tablename.

Also this guide may help you understand handling of tables.
https://www.thespreadsheetguru.com/b...t-excel-tables

Thanks for your response but that hasn't worked :-(
When I add a row to the end of my table the table doesn't expand automatically.
I still have to manually unprotected, add the table row, and re protect.

Then you need to change the password in the code to your password for the protected sheet.
Because your last sentense is exactly what the macro is supposed to handle. But if the password is not "123" you need to change it in the macro at both locations..

Yes that is what I thought but I checked it.
Here's a screen shot. Perhaps I'm missing something, as this is all new to me.
VBA code 2.jpg

it is impossible to say from a screenshot.

The only other thing you can try is changing the .Listobjects(1) to Listobjects("FillYourTablename")
as there might be another table so rows get inserted there instead of the table

if that does not work please upload an example file(remove any sensitive data) if possible.
because even with basicly 1 line of code it can be hard to see why it is not working as expected.
I did test it on a table in one of my own excelsheets.

Thanks, yeah I tried changing the table name too...
Here's an example file
Example_Correspondence Tracking Sheet Template_YYMMDD - VBA.xlsm

Ok I had a look and it works for me in the example sheet. even when I open it as readonly.

But just to be sure I changed the code a bit so the macro only works in the Data sheet
I have changed activesheet to the named sheet in the code and also used the table name in the code. with activesheet it would insert rows in any sheet with a table where you run the code.

I reattach the sheet, have another look.
Only thing I can think off is that Macro code is default disabled in your excel.
You may have to change security settings in the Trust Center

Use this link how to
https://support.office.com/en-us/art...__toc311698312

Excellent, thank-you so much. I really appreciate it.
That works now!

Hi Roel,

Is it possible to allow sorting, filtering and the use of pivot tables even though the table is protected?
I tried adding differnt variations prior to the protect sheet statement...

AllowSorting:=True, AllowFiltering:=True, AllowUsingPivotTables:=True

Self-teaching myself VBA is going take a while I guess lol

This is not a VBA issue, you got the right parameters to add to the .protect code
But what you also need to do is set up an "Allow Edit Ranges" only after setting that users will be able to sort and filter

The "Allow Edit Ranges" is the button next to the protect worksheet and protect workbook buttons.
You do not need to put this allowed range in VBA if you select the entire table (incl header) then whenever you insert a row in the table the range will expand

Great, all working now. Thanks again