Data Validation doesn't work with copy and paste?

Topics: Developer Forum
Jan 20, 2014 at 8:56 PM
My code below enable my cell to only accept 1, 2, 3 or 4, but if the user copied a invalid value and paste it in the cell, it doesn't stop them.

Since the range part of TYPE_WHOLE doesn't work on Excel5, I have to use TYPE_LIST.

Any suggestions? Thx.
                //data validation; only accept 1, 2, 3 or 4
                $objValidation = $objSheet->getCell($cellIndex)->getDataValidation();

                $objValidation->setType( PHPExcel_Cell_DataValidation::TYPE_LIST );
                $objValidation->setErrorStyle( PHPExcel_Cell_DataValidation::STYLE_STOP );
                $objValidation->setAllowBlank(false);
                $objValidation->setShowInputMessage(true);
                $objValidation->setShowErrorMessage(true);
                $objValidation->setShowDropDown(true);
                $objValidation->setErrorTitle('Input error');
                $objValidation->setError('It has to be 1, 2, 3 or 4.');
                $objValidation->setPromptTitle('Pick from list');
                $objValidation->setPrompt('Please pick a value from the drop-down list.');
                $objValidation->setFormula1('"1,2,3,4"');
Jan 22, 2014 at 7:21 AM
The problem comes from Excel and is not easy to solve. Using macros which verify what is pasted, but I saw no solutions that handle all cases, moreover, PHPExcel manages not macros in Excel 5 (the last version in development does in Excel 2007)
Jan 23, 2014 at 4:47 AM
LWol wrote:
The problem comes from Excel and is not easy to solve. Using macros which verify what is pasted, but I saw no solutions that handle all cases, moreover, PHPExcel manages not macros in Excel 5 (the last version in development does in Excel 2007)
Thank you for your replay. I did some research and it seems like Excel's own problem.