I'm working on an Excel VSTO workbook project. I source data froma SQL database and populate a seris of sheets with data from various tables. In each sheet I add a ListObject data bound to a DataTable, add some column validation, unlock certain column ranges to allow editing and then protect the sheet.
In general this works perfectly well. The problem that i am facing is that if the user applies a column filter and then tries to edit a cell, the cell value displays a value from a different column in the bound DataTable (?!). More confusingly other cells in the same row also change to show values from a different adjacent column. When I debug I can see that the bound DataTable has the correct expected value the user entered, it's just what excel display in the grid that is wrong.
It's not the first issue I've encounterd with using ListObject programmatically, but this one has me scratching my head.
There isn't really any code I can provide here as the issue presents itelf when using the native excel user interface.
So following some further experimentation, it looks as though the entire row shifts one column to the right. It just so happens that I have the first column hidden (as a work-aound to another ListObject bug I encountered). Unhiding the first column fixes the problem.
This is not exactly an answer, just yet another work-around to a bug unlikely to ever get fixed.
Seems to me like the use cases used for testing by the VSTO dev team were not as rigorous as they could have been.