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 did a work around in VBA. It allows me to keep the table set the way it needs to be for the application, and allows just those alterations I want to allow. Adding rows to a table on a locked tab turns out to be fairly easy.
It is from 2016, almost four years ago, that a reply from a Microsoft employee appeared here ... Is anyone actually reading this? If so, who is reading this and who is responsible for posting an answer? What is the breaking point when you consider the number of votes? When are you tacking? So it would be nice to know if and when this thread is eligible for implementation in O365.
In the normal course of healthy improvement of a product, one makes decisions on how to proceed (which branch to take on a subject, etc.) that turn out to be not the best one, one implements things less than wonderfully, or even adequately, and one has things simply never occur to one.
None of that should hold one back though. We are not doctors swearing to 'first do no harm': MS makes software, and should plunge ahead with things like Tables which have had enormous usage, successful usage, in the last bunch of years.
One must be committed to that backtrail. Unwise choices need corrected. Unseen things need addressed. New opportunities that never were thought of prior need taken advantage of. Basically, one cannot have regarded the done thing as finished forever, as a you get what we gave ya and nothing else, ever.
This is an aspect of that. Maybe simply not thought of. Whatever. It's a need that has been found through the enormously widespread use of Tables. Excel should address it if for no other reason than people pointing out how Mickey Mouse spreadsheets are compared to real databases, how someone's generic, $10 software database is more sophisticated and capable of handling data than the might MS Excel spreadsheet is.
(Remember when that was a thing? Even grocery stores would put up a spinner rack of dufus-looking software for $10 each? Gosh... those were the days... I mean, they must have been... right? Not sad-*ss days...)
And from the opposite direction, the more that goes into Tables and is dealt with in a more database-like manner, the easier it might be for them to interest people in PowerApps and the ton of subscription money down that road. It ain't "Penny Lane"...
So, not really a bug, per se, most likely, but certainly something that is hugely obviously needing addressing for pride and the future of MS if not for us.
The fact that it doesn't work because "protected sheets came before tables" pretty much tells me that it's a bug that nobody has bothered to fix since the inception of tables. There are over 1,300 votes for this, at what point will MS actually take action?
Travis Rose commented
Just want to add that I too think this is ridiculous and basically a bug, its barely a feature request. Help us out Microsoft! :)
Could you please post some update on the feature, is it going to be developed and released in any future updates to come?
This one would be extremely valuable, since users can do wierd stuff with Excel sheets, it needs to be partly restricted.
Of all the improvement ideas for Excel this has to be one of the most important. It certainly feels like a bug. Tables cannot be protected in any way of you wish to preserve any meaningful functionality. If it is by design then I'd like someone to explain exactly what wonderful feature is provided by such a limiting design feature?
Please change this behaviour to make tables voguish with the rest of the protection capability.
Joseph Hale commented
In addition to allowing table updates on protected sheets, it would be really nice to allow table expansion while protecting specific columns of the table (e.g. a column with a formula).
David Schwartz commented
Especially with so much work work occurring in Teams, it's crazy that we can't have a protected sheet that uses a table to keep expanding formulas down. I've used macros to get around the issue in the past, but these do not work in Teams or Sharepoint, so it's useless.
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.
Dré Stevens commented
Please Robert, share what you have. That could make my life easier until MS solves the problem. email@example.com
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.
Kanwaljit Dhunna commented
Hi Robert, Kindly share the solution please. KanwalNo1@gmail.com
Dré Stevens commented
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.