Get Tables working on protected sheets (add rows, sort, filter, etc.)
I would like to be able to, under the right circumstances, have the ability to add/remove rows of a table which resides on a protected sheet.
Currently if you protect a sheet you can't insert/delete rows of a table. Even if you unprotect all cells and allow inserting/deleting of rows on sheet protection, it doesn't matter and is completely ignored.
What I am proposing would have to follow specific rules:
- Table cells would have to be unprotected
- Cells below table would have to be unprotected (truly optional † )
- Insert Rows would have to be specified in sheet protection
† The way table rows are inserted is different than a standard row insert. If there are blank rows below the table it will "consume" those rows, and not shift things down. If there is data below the table, once it consumes all rows between the two, it will start inserting. This mechanism would make the second item above optional depending on how you [Microsoft] would handle this internally. My preference would be to have the cells unlocked, and honor the protection status of the cell, and not overwrite, or "consume" if there is space available.
This has been BROKEN for some time. Since we can ALREADY do this with standard cells, it should ALSO work for tables. This request has come up multiple times, and has previously been rejected as "BY DESIGN", but I'm here to tell you - and I'm an expert - this is not by design, and is a bug which has been overlooked and should be fixed.
(2016-12-07 Dan B [MS]: updated title to capture the actual ask here, which is to make Tables work better on protected sheets; currently, since protected sheets were built "before" Tables, they don't work well with "Tables" or objects that adjust/expand within the grid as users interact with them)
Thanks for logging this great suggestion, Zack, and to others for voting it up. We’ll prioritize this according to the number of votes, so if there’s more interest, please make sure to register your vote!
Lead Program Manager
I have noticed a number of folks coming up with ideas to get around this issue using Macros. That is all well and good IF you will have the excel document running on your desktop. However, if the workbook is shared (say in MS Teams or SharePoint) then you cannot have macros in those workbooks - and as a result this option of using macros is not available.
More and more of our workbooks are being moved to shared locations so multiple folks can edit online etc - and as a result the ability to use protected sheets where tables are incorporated is even more important.
Please fix this quickly!
Andrew Simmans commented
If you use Macros to protect and unprotect then you can stop users seeing the macros by password protecting the Macros then users can run macros but cannot see the source
I really need this: I have designed some beautiful workbooks for non-technical clients, and I'm just trying to user-proof them!
One of the actions allowed in the Protect Sheet dialog box is inserting and deleting rows and columns in a protected worksheet, but there is something odd in how this feature works.
You indicate that it is okay for people to insert and delete rows in the protected worksheet and when you try it, it cannot be done. Excel doesn't allow you to do so. This is useless in a higher level. This is just like revenge option in coc.
I have found that you can do .locked = false to unlock the new row that you just inserted but I chose not to do that (eventually) because it allowed the tab to move off the table and into the newly unlocked row. So the choice is - have the tab key move out of the table - or just use the ADD and DELETE buttons instead of the right-click insert/delete to add and delete rows... I found the buttons to be better than tabbing out of the table. Just a preference and easily settable.
Please Robert, share what you have. That could make my life easier until MS solves the problem. firstname.lastname@example.org
I am the one that posted that - I was trying to help Robert - but if you all would like to see my spreadsheet - I would be glad to share. Please keep in mind - I am not an Excel expert nor am I a teacher of any kind - I just figured this out.
Hot Rod commented
Hi Robert - Would appreciate seeing your 2-button workaround to this vexing Tables deficiency.
Hi Robert, Kindly share the solution please. KanwalNo1@gmail.com
Please Robert, share what you have. That could make my life easier until MS solves the problem.
Robert - I have added 2 buttons - Add Row and Delete Row - my spreadsheet has about 6 tables in it. I have all the tables INSERTING (instead of consuming) rows to each table based on where the ACTIVECELL is - so there is only 1 button each controlling all the tables. It actually works pretty good - obviously not as good as if this functionality was working - but still not horrible. I would be more than happy to share what I have.
How many votes do we need to get Microsoft solving this huge issue?
I love developing my workbooks with the ease of using ListObjects, writing the code around collections and built-in object methods, beautifying everything for the user, then at the end locking the sheet (to guide the user to the few Input cells they should focus on) only to THEN remember that Tables stop auto-expanding on new data when the sheet is locked, breaking EVERYTHING in the workflow/code implementation (/s). Why can't I remember that this basic functionality stops working even with 'Insert Rows' checked in protections.. Why can't I remember that in Excel, I have to manually code workarounds to the broken built-in methods. I wonder how many more times someone will look at my code and say "Why didn't you just do this?" because they don't understand the multiple roadblocks that Excel inherently contains. I should just give up.
MS needs to allow an Excel table to act more like a SQL table. Creator can set data types and locks per column (field), and still allow a user to add a row, but the (locked) formula columns calculate per the Creator's settings and cannot be changed by a user. MS excel is strong enough to become a form design tool. That is what many of us are trying to do, in a way that we can delegate routine tasks without our data being corrupted. If you can get the table security features similar to MS SQL, it could really work. Or a robust grid with built-in, concealed connections to MS SQL. One or the other.
Don Clysdale commented
Also for tables would be very useful to protect some columns that are formulas with a property on that table column.
Read this suggestion as: Allow us to protect sheets.
Asoka Walpitagama commented
I am a Excel Trainer & has been using all versions of Excel starting from Multi Plan. Its very useful specially when doing systems with Excel. If you can add another option to tick when protecting the sheet 'Allow row insert for tables' or something to that extent & make it work - I think it will be very useful
Please implement this. I design many spreadsheets for beginner Excel users and so I'm faced with them either a) messing up my formulas in calculated columns, b) having to inject VBA code as a workaround or c) deal with new data reside outside a table and try to show them how to fix it themselves. All of which are bad options. Not sure why this hasn't been implemented by now
Marcos Rieper commented
Found a partial workaround to 'append' data but determining the size of the table and subsequently using range/cells/offset to get an entry right below. The table and any column formula will create the new record with the auto-expand function, from which point on Listobjects() can be used to further add data to that and any other records. The UserInterfaceOnly:=True in the Thisworkbook module's Workbook_Open macro sets the prerequisite for macros to be allowed the manipulation on protected sheets with locked cells.