locked
Range.Validation.Add (Type: XlDVType.xlValidateList, ...) generates an exception if the list contains more than 255 characters. RRS feed

  • Question

  • Range.Validation.Add (Type: XlDVType.xlValidateList, ...) generates an exception if the list contains more than 255 characters.

    Excel Version (MS Excel 365 Business MSO(16.0.11328.20140) 64-Bit)

    privateCultureInfo _cultureInfo;

    privatestring_listSeparator;

    public void OnStartButton_Click() {

    _cultureInfo = CultureInfo.CurrentCulture;

        _listSeparator = _cultureInfo.TextInfo.ListSeparator;

    List<string> items = new List<string>(); for (int i = 1; i < 100; i++) { items.Add(string.Format("Part{0}", i)); }

    int rowIndex = 1; int columnIndex = 1; for (int i = 1; i < 100; i++) { Range range = _workSheet.Cells[rowIndex, columnIndex]; try { Trace.WriteLine(string.Format("Range: Row {0} Column {1}", range.Row, range.Column)); List<string> actItems = items.GetRange(0, i); string flatList = string.Join(_listSeparator, actItems.ToArray()); Trace.WriteLine(flatList); Trace.WriteLine(flatList.Length);

    range.Validation.Delete(); range.Validation.Add( Type: XlDVType.xlValidateList, AlertStyle: XlDVAlertStyle.xlValidAlertInformation, Operator: XlFormatConditionOperator.xlBetween, Formula1: flatList, Formula2: Type.Missing);

    range.Validation.IgnoreBlank = true; range.Validation.InCellDropdown = true; range.Value = actItems.Last(); } catch (Exception ex) { Trace.WriteLine(ex.Message); MessageBox.Show(string.Format("Error on {0}", i)); break; } columnIndex++; } }



    • Edited by Michael Jütte Monday, March 18, 2019 2:48 PM CurrentCulture
    Monday, March 18, 2019 2:32 PM

All replies

  • Unfortunatly,

    there are still some 255 lenght contrains in Excel and it looks like this is one of them.

    regards

      Hendrik Schmieder

    Tuesday, March 26, 2019 10:58 AM