Data Validation to prevent a blank cell

  • Thread starter Thread starter Bob
  • Start date Start date
B

Bob

Excel 2002

I'm using Data Validation for the 1st time and I'm wondering if this feature
is capable of preventing users from deleting (with the keyboard Delete key)
a default value in a cell (8) unless they replace it with a number (decimal
permitted) between 1 - 24 (representing hours in a day).

With the Ignore Blank Checkbox unchecked, when I enter any number not
between 1-24 I get the hoped for validation message, but when I delete the
default value using the keyboard Delete key I don't get the hoped for
validation message.

I do get the validation message when I use the keyboard Backspace key to
delete the default value, but that's only minimally helpful since I don't
think most users use this method of deleting cell contents. Similarly, I
also get the validation message when I double click on the cell (in cell
editing is on), place the cursor to the left of the value, and use the
Delete key to delete the value, but this is also an unlikely method of
deleting the cell's contents.

I'd rather not resort to a VBA solution. I'm hoping there's a custom formula
solution of some other way to get Data Validation to produce the desired
result. It seems to me this is a rather common desired result for Data
Validation. It's difficult to imagine it isn't up to the task. Any
suggestions that get me closer to my goal are appreciated.

Bob
 
Hi bob
AFAIK this is not possible to prevent without using VBA (e.g. an event
procedure).
 
You could use conditional formatting in conjunction with the data
validation. For example, turn the other cells in the row to black fill
if the cell with data validation is blank.

If the cell with data validation in cell A2, select cells B2:H2
Choose Format>Conditional Formatting
From the first dropdown, choose Formula Is
In the formula box, type: =$A2=""
Click the Format button, and on the Pattern tab, select the black colour
Click OK, click OK
 
Back
Top