Data Range Validation

Questions and answers on how to use XLSReadWriteII 5.
Post Reply
mjb1416
Posts: 1
Joined: Mon Mar 27, 2023 3:04 pm

Data Range Validation

Post by 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
Posts: 1
Joined: Tue May 16, 2023 8:31 am

Re: Data Range Validation

Post by 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
Post Reply