IDataValidation with DataRange on different sheets

[Michele Montanari]

Hi,

I have used a IDataValidation interface to make sure a cell value is in a range of possible values.

I Have used the ListOfValues member, but it seems to throw an exception when the total characters of the String Array is more than 256.

In the Excel Interface for that  operation, the input of characters is stopped at that length.

So I am trying to use the DataRange Property to add the List of Values.

I have created a second sheet, Hidden to put the values.

Whan I try to set the DataRange Property to that range (that is on a different sheet) There is an exception that says that the cells must be on the same sheet.

Excel permits to do that so there is a method to do it with XlsIO?

For this particular operation I am still using an older version of Dlls (12.2.0.36). Maybe the newer version don't have this problem.

    Thanks

   Andrea Perazzolo

[Ishwarya Narayanan]

Hi Michele,

 

Thank you for using Syncfusion products.

 

Regarding “String array is more than 256” Exception

In XlsIO, limitation of  listOfvalues property  is 255 characters including separators, when it exceeds the limits xlsIO throws Exception. If you want to add more characters, you could use the following code snippet. Code Snippet: //Add data to cells sheet.Range["A1"].Value = "Iam 20 letter string"; . . . . sheet.Range["A15"].Value = "Iam 20 letter string";   //Sets the allow type of validation to list validation.AllowType = ExcelDataType.User;   //Source for the above list validation.DataRange = sheet.Range["A1:A15"];

Regarding “Cell must be on the same sheet” Exception

Kindly, use the following code snippets to  refer  the ranges from hidden sheets. We have shared the code snippet and  sample for your reference. Please let us know if it resolves the issues.   Code Snippet:   workbook.Allow3DRangesInDataValidation = true;     Sample Link: http://www.syncfusion.com/downloads/support/directtrac/general/Forum_117858_IDataValidationwithDataRange1691635298.zip

 

Please let us know if you need any clarification.

 

Regards,

Ishwarya N