Auto Protect/Lock Dynamic Table After New Entry
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?
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.
Facing such issue..
Israr Ahmad commented