How to make excel cells mandatory?

Question

I'm trying to make some excel cells mandatory so that a message can be displayed, if they are left blank by the user.

Workbook workbook = new XSSFWorkbook();
Sheet sheet = workbook.createSheet("Data Validation");

DataValidationHelper dataValidationHelper = sheet.getDataValidationHelper();
DataValidationConstraint lengthConstraint = dataValidationHelper.createTextLengthConstraint(
        DataValidationConstraint.OperatorType.BETWEEN, "2", "45");

CellRangeAddressList cellList = new CellRangeAddressList(0, 0, 1, 1);
DataValidation validation = dataValidationHelper.createValidation(lengthConstraint, cellList);

validation.setErrorStyle(ErrorStyle.STOP);
validation.createErrorBox("Error", "The length must be between 2 and 45.");
validation.setEmptyCellAllowed(false);

if (validation instanceof XSSFDataValidation) {
    validation.setSuppressDropDownArrow(false);
    validation.setShowErrorBox(true);
} else {
    validation.setSuppressDropDownArrow(true);
}

sheet.addValidationData(validation);

Row row = sheet.createRow(0);
Cell cell = row.createCell(1);
cell.setCellValue("Text");

I used validation.setEmptyCellAllowed(false); and expected that it should prevent cells from being emptied but it does not work. The cell/s on which this constraint is enforced are however, validated for length which is between 2 and 45 characters.

Why does validation.setEmptyCellAllowed(false); not work in this case? How to make a cell mandatory so that it cannot be left blank?

---

When the validation.setEmptyCellAllowed(false); method is used, it seems the validation constraint is correctly applied in Excel.

The checkbox Ignore blank is unchecked. Excel still allows the cells on which this constraint is enforced to be left blank.

The purpose of Ignore blank in Excel may be different that I do not understand. Anyway I need to make certain cells mandatory. If an attempt is made to empty them then, it should be rejected and an appropriate error message should be displayed. It may require some Visual Basic tricks/code to be embedded in Excel.

Answer 1

Ignore blank does not accomplish what you want it to. In a situation as you describe, when the user is typing into the cell, it prevents him creating a blank entry. However, it does NOT prevent him exiting data entry mode (e.g. using ESC) which will also leave the cell blank. It also does not prevent him from selecting the cell, and hitting Delete without going into data entry.

Depending on exactly what you want to do, you can use a VBA event macro to test whether the cell is blank after a certain event. Whether this should be a Worksheet_SelectionChange event, Worksheet_Deactivate event, Workbook_Close event, or one or more other events, depends on the specifics of your project.