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
Dré Stevens commented
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
You could add a Private Sub Worksheet_TableUpdate(ByVal Target As TableObject)
event macro and/or create a userform for the input to append to your table with an _afterupdate macro as part of the userform code. Setting protection on a sheet can include UserInterfaceOnly:=True when the Workbook is loaded for the sheets of choice, so the macro/userform can make the change without having to unprotect/unlock the sheet or table range.
Works brill for me on a distributed workbook. Specifically, since macros have no permission to change tables such as listrow.add, the userform must append the data to the table. The table will then automatically expand to include the new line.
With UserInterfaceOnly:=True set there's very little not allowed on a protected sheet, changing tables sizes (exception wishlisted) and pivottable cache refreshes are just the few I've come across which need programmatic temporary unlocking, do whatever, and relock for routine user activities.
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.
Facing such issue..
Israr Ahmad commented
Sayam Rafique commented
Microsoft Excel my goal is to make the whole sheet uneditable and editable both at the same time.
For instance, I'm using Table1 and with every new entry, one row adds up in the table, but the whole table is prone to risk of being mistakenly or deliberately removed/edited. I want to make a new entry (which will add a new row, aa usual) but the whole should remain protected/locked (unchangeable). Have i conveyed my point?
Just ran into this brick wall. Microsoft pitches using tables and structured references and then gives no way to lock the formulas down in an expandable table except data validation (slows things down so much it takes a few seconds to add a row) or VBA. If Microsoft wants large organizations to work their way up to power BI, it'd be a lot easier if I could demonstrate with tools mgt already has confidence in (e.g. tables, spreadsheets, etc.)
Robert Robson commented
This is 7th most voted open idea on the list and the comment from the admin didn't change for almost 2 years.
Simon Hurst commented
The discussion on using VBA and Data Validation shows just how important it is for Microsoft to do this properly rather than forcing people into messy workarounds.
Miles Wolbe commented
@Pavlo Burchenko: Yes, the error checking rules apply to the local copy of Excel where they are set. However, you can disable error checking on workbook open and then restore the user's previous settings on workbook close with a little VBA: https://www.ozgrid.com/forum/index.php?thread/29089-disable-error-checking-via-vba/&postID=1035971#post1035971 .
Still looking for a way to disable just "Data entered in a table is invalid" with VBA. It doesn't appear to be one of the properties offered in the ErrorCheckingOptions object: https://docs.microsoft.com/en-us/office/vba/api/excel.errorcheckingoptions . The closest I could find, "Application.ErrorCheckingOptions.InconsistentTableFormula = False" disables "Incosistent calculated column formula in tables" instead.
Pavlo Burchenko commented
@Miles Wolbe: Thanks for the links!!! Regarding Data Validation workaround, hinding error notifications will only work for your workstation. Other users will see the error notifications it once opened on their workstations. This is because such settings are local and independent from the file.
Miles Wolbe commented
@DMT: Thanks for the heads up. You're right; while the data validation hack works to protect cells from users typing into them, it does not prevent users from pasting into them. However, an error is displayed with an option to "Restore to Calculated Column Formula", even if the "Data entered in a table is invalid" error checking rule was disabled as previously described.
@Miles Wolbe - but the data validation is not working, since users can just copy-paste on top of validation and over-write all your rules.