How to create validation list using XLSReadWriteII5

Question

I need to create validation list in Excel using component TXLSReadWriteII5

I have written the following code but for some reason validation list is not getting created in Excel file. Thanks in advance.

var
  LwWorksheet : TXLSWorksheet;
  LValidation: TXLSDataValidation;
  LCell  :TXLSCell;
begin
  LwWorksheet:= XLS.Add;
  try
    LwWorksheet.Name:= 'This is a test';
    LwWorksheet.AsString[1,1] := 'This is a test';

    LwWorksheet.AsString[3,2] := 'A';
    LwWorksheet.AsString[3,3] := 'B';
    LwWorksheet.AsString[3,4] := 'C';

    LwWorksheet.AsString[2,2] := 'A';
    LwWorksheet.AsString[2,3] := 'B';
    LwWorksheet.AsString[2,4] := 'C';

    LwWorksheet.AsString[4,2] := 'A';
    LwWorksheet.AsString[4,3] := 'B';
    LwWorksheet.AsString[4,4] := 'C';

    LValidation:= LwWorksheet.Validations.Add;
    LValidation.Areas.Add(5,5);
    LValidation.Formula1:='$B$2:$B$4';
    LValidation.Sqref.Add(5,5);
    LValidation.Type_:= x12dvtList;
    LValidation.ShowDropDown:= True;
    LValidation.ShowInputMessage:= True;
    LValidation.AllowBlank:= False; 
  finally
    LwWorksheet.Free;
  end;//try...finally...

The validation is actually being added (you can see by setting the `Prompt` and `PromptTitle` properties and then clicking on cell F6). It still seems to not be setting the value for `ShowDropDown` properly, which was supposedly corrected in v5.0.13; as I just tested with the demo v5.0.28a and can reproduce the problem, it seems to have reappeared. — Ken White, Jan 21 '15 at 20:18
To add information, I recorded a macro in Excel that created the same validation in an adjacent cell, and it sets the same properties (some named slightly differently, but recognizable as being the same) and the drop down appears as it should; that should confirm that it's XLSRwII5 that isn't properly writing the value. You might want to edit that information into your post at the Axolot forums. — Ken White, Jan 21 '15 at 23:04

How to create validation list using XLSReadWriteII5

0 Answers0