Undoing Excel table AutoExpansion for rows not raising workbook's SheetChange event

Question

I'm working on a sort of a cache for Excel tables (list objects) and noticed this issue with tables and the workbook SheetChange event in Microsoft.Office.Interop.Excel.

Everything is okay when undoing column AutoExpansion:

If you enter some data in a column next to a table, the data and the table AutoExpansion raise a SheetChange event and the table expands. The first undo will undo the AutoExpansion and raise the SheetChange event and the second undo will undo the data entered and raise a second SheetChange event.

The same fails/does not work as expected for rows:

If you enter some data in a row below a table, the data and the table AutoExpansion raise a SheetChange event and the table expands. The first undo will undo the AutoExpansion and will not raise a SheetChange event! The second undo will undo the data entered and only then is a SheetChange event raised.

I've added a GIF replicating the behavior (I am using QueryStorm to display the logging stuff you see on the bottom right of Excel):

This is an issue for me because I have to closely monitor any changes happening to (or around) an Excel table in order to make the "cache" work, but when someone undos a table AutoExpansion for rows the event is not raised and I am not able to handle the change accordingly.

Is there any workaround for this issue? If this is indeed a bug, will it be fixed in the future?

Thanks in advance!

Answer 1

In fact, this is a normal situation, and the behavior of column auto-expanding is the same as that of row auto-expanding.

The SheetChange event does not care about the format change, it only cares about the actual content change.

When adding a row, the format of A5~C5 has changed, the content of B5 has changed, the event is triggered once, when you undo, the format of A5~C5 has changed, but there is no cell content change, so it will not be triggered at this time.

When adding columns, please note that when the content of D3 changes, D1 also automatically adds a column name, so the SheetChange event is triggered twice. We can also see this behavior in the GIF you provided.

When undoing, the column name of D1 disappears and the event is triggered once.

---

If the response is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.