none
Removed Feature: Data validation from /xl/worksheets/ RRS feed

  • Question

  • Hi All,

    We are using VSTO for Excel and we have code something like this

    TabName.AddColumnValidation(COLUMN_INDEX,"String1, String2........,StringN","Please select from list");

    public static void AddColumnValidation(this ListObject listobject, int column, string validationValues,
                                                   string errorMessage)
            {
                Excel.Range body = listobject.GetColumnRange(column);
                if (body != null)
                {
                    body.Validation.Delete();
                    body.Validation.Add(Excel.XlDVType.xlValidateList,
                        Excel.XlDVAlertStyle.xlValidAlertStop,
                        Excel.XlFormatConditionOperator.xlBetween,
                        validationValues);
                   
               
                }
            }

    The validationvalues are more than 255 in length and we face Issue when we are saving the excel file and opening it with excel 2013. The Error is 'Feature: Data validation from /xl/worksheets/sheetName.xml'

    Is there any workaround for this with the code in VSTO using C# to set more than 255 char length for a column so that when saving the excel and opening do not cause an issue.

    Any Help would be Appreciated!

    Wednesday, December 18, 2013 12:21 AM

Answers

  • Hi,
    I reproduced your issue in my side, Did you add the validateList value in your Formula1 argument directly?
     
    For the workaround, You can save the list into excel ,then you should get the value from the specified range list in Excel.
    Here is a sample for your reference:
    string formula = "='Sheet1'!B1:B100";
    body.Validation.Delete();
    body.Validation.Add(Excel.XlDVType.xlValidateList,
                         Excel.XlDVAlertStyle.xlValidAlertStop,
                         Excel.XlFormatConditionOperator.xlBetween,
                         forumula, Type.Missing); 

    Regards,

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Marvin_Guo Tuesday, December 24, 2013 8:48 AM
    Thursday, December 19, 2013 1:36 AM

All replies

  • Hi All,

    We are using VSTO for Excel and we have code something like this

    TabName.AddColumnValidation(COLUMN_INDEX,"String1, String2........,StringN","Please select from list");

    public static void AddColumnValidation(this ListObject listobject, int column, string validationValues,
                                                   string errorMessage)
            {
                Excel.Range body = listobject.GetColumnRange(column);
                if (body != null)
                {
                    body.Validation.Delete();
                    body.Validation.Add(Excel.XlDVType.xlValidateList,
                        Excel.XlDVAlertStyle.xlValidAlertStop,
                        Excel.XlFormatConditionOperator.xlBetween,
                        validationValues);
                   
               
                }
            }

    The validationvalues are more than 255 in length and we face Issue when we are saving the excel file and opening it with excel 2013. The Error is 'Feature: Data validation from /xl/worksheets/sheetName.xml'

    Is there any workaround for this with the code in VSTO using C# to set more than 255 char length for a column so that when saving the excel and opening do not cause an issue.

    Any Help would be Appreciated!

    • Merged by Marvin_Guo Wednesday, December 18, 2013 6:44 AM the same
    Wednesday, December 18, 2013 12:20 AM
  • Hi,
    I reproduced your issue in my side, Did you add the validateList value in your Formula1 argument directly?
     
    For the workaround, You can save the list into excel ,then you should get the value from the specified range list in Excel.
    Here is a sample for your reference:
    string formula = "='Sheet1'!B1:B100";
    body.Validation.Delete();
    body.Validation.Add(Excel.XlDVType.xlValidateList,
                         Excel.XlDVAlertStyle.xlValidAlertStop,
                         Excel.XlFormatConditionOperator.xlBetween,
                         forumula, Type.Missing); 

    Regards,

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by Marvin_Guo Tuesday, December 24, 2013 8:48 AM
    Thursday, December 19, 2013 1:36 AM
  • string formula = "='Sheet1'!$B$1:$B$100";
    body.Validation.Delete();
    body.Validation.Add(Excel.XlDVType.xlValidateList,
                         Excel.XlDVAlertStyle.xlValidAlertStop,
                         Excel.XlFormatConditionOperator.xlBetween,
                         forumula, Type.Missing); 
    For anyone else that might be doing this for a range of cells, don't forget the $'s in the formula.
    Friday, August 22, 2014 8:01 PM