none
DataTable events not getting fired when selecting values for drop down list. RRS feed

  • Question

  • Hi,

    I have 2 tables, second table's first column shows data validation list (values are from first table). When I insert a new row and select a value from drop down list and press enter, none of the data table events are being fired. If i enter the value manually instead of selecting from drop down list, every thing works fine and events are getting fired.

    My Excel Project

    Steps to replicate the issue:

    Compile and Run above project.

    After excel opens, Go to TestSr tab-> Click on Try Me button (New sheet should get created with 2 tables in it)

    Select cell "E3" and choose any value from the drop down list and press enter, notice that TableNewRow event is not fired. Now try to edit cell "F3", RowChanged/ColumnChanged events won't get fired either.(This is the issue

    Now start from the scratch and run the project again and follow the same steps, but instead of selecting a value from combo box in cell "E3", manually type in a value and press enter, now notice that TableNewRow gets fired.

    BTW, this issue occurs only when validation list is from another range. If the pick list values are just a string with comma seperated values, things works fine. Let me know if any info is needed.

    PS: Project was built and compiled using Visual Studio 2012.

    Thanks






    • Edited by Suresh700 Wednesday, January 3, 2018 8:28 AM
    Saturday, December 30, 2017 8:56 AM

All replies

  • Hi Suresh700,

    you had mentioned that,"When I insert a new row and select a value from drop down list and press enter, none of the data table events are being fired. If i enter the value manually instead of selecting from drop down list, every thing works fine and events are getting fired."

    did you try to check whether the value is passed correctly from combobox or not.

    if you did not check it then you can check it.

    other thing you can do is use the combobox events to call your datatable events.

    further you can share your sample project here, we will try to make a test with it and try to reproduce the issue.

    then we will try to provide you further suggestions to solve the issue.

    Regards

    Deepak  


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, January 1, 2018 6:50 AM
    Moderator
  • Hi Suresh,

    Could you share us a simple project with the excel which could reproduce your issue?

    I made a test with your code to try to reproduce your issue, it broke on some lines and I could not get the same result as yours.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 2, 2018 6:44 AM
  • Thanks for the reply. I have updated the question with the link to project and I have also listed the steps to replicate the issue.

    Also, the combo box is a data validation list(not ActiveX combobox). How can I check if value is being passed correctly? 

    Wednesday, January 3, 2018 8:32 AM
  • Thanks for the reply Tao, I have updated the question with a link to project and I have also given the steps to reproduce the issue.
    Wednesday, January 3, 2018 8:33 AM
  • Hi Suresh,

    Thanks for the update information.

    I made a test, and I could get the same result.

    I assume this is caused by the conflict between DataTable event and Range Validation.

    As you found, the event could fire for normal cells.

    For a workaround, I would suggest you remove the data validation on the column, and check whether the value is validate on TableColumn_OnChanged event or Worksheet_Change event.

    If you have any trouble to implement custom validation on these events, please feel free to post a new thread, and we will try to provide the exact code for you based on your requirement.

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, January 3, 2018 9:47 AM
  • Hi Tao,

    I can check if value is valid or not in TableColumn_OnChanged event or in Worksheet_Change event. But what about drop-down list? With this option, I won't be able to show drop-down list right? 

    PS: Drop-down list is a strict requirement in the project I am working with  :(


    • Edited by Suresh700 Wednesday, January 3, 2018 12:07 PM
    Wednesday, January 3, 2018 12:07 PM
  • Hi Suresh,

    >>Drop-down list is a strict requirement in the project I am working with

    If the Drop-down list is requirement, will it work if you enable Range Valiation and use Worksheet_Change event instead of table event?

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Thursday, January 4, 2018 8:23 AM
  • Yeah, using Worksheet_Change event should work. Let me try using worksheet change event and will update the thread based on the result.
    • Edited by Suresh700 Friday, January 5, 2018 5:04 AM
    Friday, January 5, 2018 5:03 AM
  • Hi Suresh700,

    What is the result of test?

    Did Worksheet_Change event work for you?

    If your issue has been resolved, I would suggest you mark the helpful reply as answer to close this thread.

    If not, please feel free to let us know.

    Regards,

    Tony


    Help each other

    Monday, January 8, 2018 8:34 AM
  • Hi,

    Sorry for late reply. I tried using worksheet_change event. This is not working either. 

    This is what I am doing in my project, I make all the changes required in the table and then there is a button in the ribbon to publish all the changes made and send the data to server. With the above problem(mentioned in question), the row itself is not being added to the datatable (it shows in excel table though). When I follow the above steps and click on publish->check for number of rows in datatable, it doesn't show the row that was inserted using steps mentioned in the question (May be that's the reason why its not firing any event?) I can handle stuff in Worksheet_Change event provided that the row is inserted in datatable (If this works, then my problem will be fixed).


    • Edited by Suresh700 Tuesday, January 9, 2018 5:18 AM
    Tuesday, January 9, 2018 5:17 AM
  • Hi Suresh,

    >> I make all the changes required in the table and then there is a button in the ribbon to publish all the changes made and send the data to server.

    Do you need to trace all the changes, or you only need the result for the table?

    >> check for number of rows in datatable, it doesn't show the row that was inserted using steps mentioned in the question

    Which code did you use to get datatable?

    I suggest you make a test with code below.

            public void PublishTables(Office.IRibbonControl control)
            {
                var worksheet = (Worksheet)Globals.ThisAddIn.Application.ActiveSheet;
                var workSheetObject = Globals.Factory.GetVstoObject(worksheet);
                const string tableName2 = "TestTable2";
                System.Data.DataTable data = (System.Data.DataTable)vstoObject2.DataSource;
                var range = vstoObject2.DataBodyRange;
                var secondTable = new System.Data.DataTable("SecondDataTable");
                secondTable.Columns.Add(new DataColumn("UserName", typeof(string), "") { Caption = "User Name" });
                secondTable.Columns.Add(new DataColumn("Address", typeof(string), "") { Caption = "Address" });
                for (int i = 1; i < range.Count; i++)
                {
                    DataRow dataRow = secondTable.NewRow();
                    dataRow["UserName"] = range.Cells[i,0].Value;
                    dataRow["Address"] = range.Cells[i, 1].Value;
                    secondTable.Rows.Add(dataRow);
                }
                secondTable.AcceptChanges();
    
                Microsoft.Office.Tools.Excel.ListObject vstoObject = workSheetObject.Controls.AddListObject(worksheet.Range["G2"], "Result");
                vstoObject.AutoSetDataBoundColumnHeaders = true;
                vstoObject.SetDataBinding(secondTable);
    
            }
    

    Best Regards,

    Tao Zhou


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, January 23, 2018 7:12 AM