Validation of blank cells

[Blue Max]

We cannot get Excel to issue a validation error when a value is deleted from
a cell. The cell validation is set up to only accept the whole number 39.
In addition, the option to ignore blanks has been manually unchecked.
Finally, we have created a validation alert message in the event the wrong
value is input.

Since the option to ignore blank cells is unchecked, we assumed that
deleting the proper value from the cell (blank cell) would generate a
validation error message, but it does not. How do we get validation to
alert us when a valid value has been deleted from a cell?

 

[Gary''s Student]

First put 39 in some cell, say Z100.

Click on a cell, say A1, and make the Data Validation > List and the list
the single cell Z100.

If the user tries to place a value in A1, only 39 will be allowed.

Once entered, the value cannot be cleared by the user clicking on A1 and
using the BACKSPACE key.

 

[Blue Max]

Thank you 'Gary's Student'. A great suggestion, but still have two
questions:

FIRST, do you happen to know why the first approach will not work? It would
seem reasonable to assume that the blank cell (since not ignored) would
trigger an alert when the value is deleted.

SECOND, can't the list or value be entered as an array in the validation
dialog 'list' field without needing to refer to another cell (such as Z100)?

Thanks

**********************

 

[Gary''s Student]

The list approach will work withone or more items - just be sure to make them
ALL 39