Protected Sheet - Tables Problem

[lyla22]

Hi,

I have a workbook in which I've created a menu (using Hyperlinked shapes) in each worksheet. Some of the sheets contain tables of data which feed chart sheets. All works fine
until I protect the sheet and the table no longer auto-expands with new data and therefore the related charts dont update.

So I'm looking for a way which avoids sheet protection (so that my tables can update), but stops users from moving/changing the menu area shapes and hyperlinks. Ideally, I'd prefer to lock all cells apart from the table but I can't see how I'd do that without sheet protection.

I've done a lot of searching and I can see that the issue with tables not updating on Protected sheets is a known issue with no work-around (that I could find).

I'd really appreciate any help.
Thanks.

[TMS]

The only way I know is to use VBA. A combination of Worksheet Selection Change and Worksheet Change events. Use Selection Change to stop people selecting "protected" cells/ranges, and/or store contents of cells, and Change to undo any changes in those, or other, protected areas.

Alternatively, I've seen people recommend having a button to insert new rows in the protected table.

Regards, TMS