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.

enter image description here

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.

score 7 · Accepted Answer · edited Sep 29 '14 at 21:32

7

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.

edited Sep 29 '14 at 21:32

Tiny

27,221
105
339
599

answered Sep 27 '14 at 19:52

Ron Rosenfeld

53,870
7
28
60

1

I know of no solution to your problem that can be accomplished with just native Excel functionality. However, VBA (not VB) gets installed whenever Excel is installed. The running of Macros can be restricted by Security policies. Try this: With Excel open, right click on a sheet tab and select View Code. If a code window opens, then VBA is installed. – Ron Rosenfeld Sep 27 '14 at 20:24
@Tiny Flagging my comment – Ron Rosenfeld Sep 27 '14 at 20:35
@Tiny Then you should be able to use the appropriate Event codes, unless Security Policies preclude running macros. Exactly which to use depends on specifics, as I wrote in my answer. – Ron Rosenfeld Sep 27 '14 at 22:11
Perhaps I will go without this validation, since there a learning curve. I never worked with VBA and I'm completely unfamiliar with Visual Basic. Can the language be changed to C# anyway? – Tiny Sep 29 '14 at 21:30
@Tiny I don't understand your question about changing VBA to C#. – Ron Rosenfeld Sep 30 '14 at 02:09
The language looks very close to Visual Basic (again I do not know anything about it). Therefore, I assumed it to be that language and thought that it might support a different language like C# :) – Tiny Sep 30 '14 at 03:41
1

VBA (Visual Basic for Applications) is different from Visual Basic (and C#) in many respects. And as far as I know, there are no other languages that can be used within Excel-VBA modules -- only VBA – Ron Rosenfeld Sep 30 '14 at 10:42
@RonRosenfeld . Of course VBA is a language, but it is an extensible environment that can utilize objects that conform to COM specifications. Those components can be written in just about any language. So indirectly one can use code written in other languages. On a side note VBA can use the MS ScriptControl object. ScriptControl allows one to interact with VBScript and JScript from within VBA. I recently answered a [bounty question](http://stackoverflow.com/questions/26037393/vba-javascript-object-doesnt-support-this-property-or-method/26132020#26132020) that involved VBA/JScript – Michael Petch Oct 04 '14 at 15:39
@MichaelPetch Thank you for that expansion. Perhaps that information will be useful to the original poster. I thought he was asking about using C# directly. – Ron Rosenfeld Oct 04 '14 at 16:28

score 0 · Answer 2 · edited Apr 13 '15 at 07:08

0

Try with the following, it worked for me!!!!

DataValidationConstraint lengthConstraint = validationHelper.createTextLengthConstraint(
                DataValidationConstraint.OperatorType.GREATER_THAN, "0", "");

edited Apr 13 '15 at 07:08

Tiny

27,221
105
339
599

answered Apr 13 '15 at 06:47

Masud Ahamed

19
2

third argument for the method "createTextLengthConstraint" will throw exception at run time. – Madhusudan Dec 02 '15 at 14:27

How to make excel cells mandatory?

2 Answers2