Data Range Validation

[mjb1416]

I'm trying to set a set of cells to only accept dates in a certain range. My code

FXLS.CmdFormat.Mode := xcfmReplace;
FXLS.CmdFormat.BeginEdit(DataSheet);
FXLS.CmdFormat.Number.Format := 'YYYY-MM-DD';
FXLS.CmdFormat.Apply(Col, FIRSTDATA_ROW, Col, AVAILABLE_ROWS - 1);

dv := DataSheet.Validations.Add;
dv.Areas.Add(Col, FIRSTDATA_ROW, Col, AVAILABLE_ROWS - 1);
dv.Type_ := x12dvtDate;
dv.Operator_ := x12dvoBetween;
dv.Formula1 := '1900-01-01';
dv.Formula2 := '2499-12-31';
dv.ShowErrorMessage := True;
dv.ErrorTitle := 'Invalid Value';
dv.Error := 'Valid date required in ISO format between 1900-01-01 and 2499-12-31';

Does not work. It will not accept any dates that are valid in the range. I've also tried dates in UK format DD/MM/YYYY.

What is the correct method of doing this?

Mike

[Geert]

Mike,

I had exactly the same problem.
It occurs because XLSReadWrite adds an '=' in front of the Date values.
This is how I fixed it:

xlsValidation.Formula1 := 'DATEVALUE("01/01/1900")';
xlsValidation.Formula2 := 'DATEVALUE("31/12/2099")';


Kind Regards,

Geert