Validating Cells

  • Thread starter Thread starter Discovery
  • Start date Start date
D

Discovery

When validating cells, how do I set it so that you cannot leave a cell
blank, i.e. it has to contain a number, even if the number is zero. I
want a warning message to come up - I know how to do this, but I've
tried different criteria and it always seems to allow having a blank
cell, when I just want a zero in it instead, if it is to be blank.
Please can someone help me. I hope this makes sense.

Thanks,
Discovery
 
No matter what data validation settings you've applied, users will be
able to leave the cell blank, or select the cell, and press the Delete
key, to clear the cell.

However, if the cursor is in the cell, or in the formula bar, they won't
be able to press the Delete key, to clear the cell, or press the Enter
key while the cell is blank.

You could use programming to display an error message when users try to
save the workbook, or switch to a different sheet.

Or, you could modify some of your formulas, to display a message if the
cell is blank. For example:

=IF(B5="","Please enter a value in cell B5",SUM(D2:D4))
 
Discovery,

Consider this: If entering a cell, the user isn't allowed to leave it, the
user might be able to get into a no-win situation where he's selected a
cell, perhaps by accident, and can't get out. He'll be stuck, and he'll
start saying horrible things about you in the break room.

You can use the Worksheet_Change event to get more detailed control over
what the user can do.
 
Back
Top