none
Excel ListObject column events? Weird GetVstoObject() behaviour? RRS feed

  • Question

  • Hi,

    I'm developing an Application-level addin for Excel.

    The application needs to build/delete/rebuild list objects, while handling user changes to the list object (add/remove/move/rename columns, etc).

    These list objects are not data bound, due to specific behavioral requirements.

    I'm running into 2 problems.

    First problem:

    How can I intercept the following ListObject events?

    1. Column header renamed
    2. Column added
    3. Column removed
    4. Column moved

    I've converted my Microsoft.Office.Interop.Excel.ListObjects to Microsoft.Office.Tools.Excel.ListObject.

    And I'm using the Selected and Changed events to detect changes, but this solution involves a lot of hacks (remembering state during Selected, and compare with Changed).

    Is there an easier way to intercept the events I've listed above?

    Second problem:

    If I were to delete a ListObject, and recreate a new one with the same name, calling Globals.Factory.GetVstoObject() on the new ListObject throws:

    The control cannot be added because a control with the name XXXXXX already exists in the Controls collection.

    What should I do in this scenario?

    Thanks

    MC

    Wednesday, February 27, 2013 2:26 AM

All replies

  • Hi Min Chew,

    Thank you for posting in the MSDN Forum.

    >First problem:

    The available ListObject Events can be seen in this page, since there's no events that can be directly used to handle below events

    • Column header renamed
    • Column added
    • Column removed
    • Column moved

    , all I can suggest might be the same as you've used.

    >Second problem:

    Could you please create a sample project that can reproduce your scenario? You can then upload it to SkyDrive and share it to us.

    I look forward to your reply.

    Best regards,


    Quist Zhang [MSFT]
    MSDN Community Support | Feedback to us
    Develop and promote your apps in Windows Store
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Thursday, February 28, 2013 11:07 AM
    Moderator
  • I'm experiencing the exact same problem. 

    To get events ColumnAdded, ColumnRemoved, CellChanged, RowAdded, RowRemoved, RowInserted I ended up writing a wrapper to ListObject which did just as you said - it listens to Changed, SelectedChanged events and looks at previous listobject Range size, current ListObject range size and Target Range. I discovered rules which preceed all these events and covered the whole lot in about 50 unit tests. 

    I'm sorry I'm unable to share the code since its covered under NDA, but just wanted to say the approach which you propose is the best (and only) one. 

    Regarding the error 

    The control cannot be added because a control with the name XXXXXX already exists in the Controls collection.

    We are experiencing this same thing, but with a different trigger. If we have a ListObject (table) in the excel worksheet and a column has list-validation, then at the bottom of the ListObject we type in a cell immediately below the list-validation column, we would expect this to create a new row in the ListObject and give us the validation popup saying 'Retry-Cancel'.

    Now it does give us the validation popup however when we click 'Cancel' and from this point on our application stops working. 

    We discovered that the ListObject internally was recreated by VSTO (with no warning) and the old listObject which we had subscribed our Changed events is no longer in the Worksheet, and has had its HeaderRowRange and DataBodyRange set to null. Basically the entire thing self-destructs. 

    By trying to workaround this problem we encoutered the above 'Control cannot be added' exception. Basically we're stuck between a rock & a hard place! ... 

    Monday, May 13, 2013 3:27 PM