Data Validation to only accept Y and N and NO blank

Hello -

I have been trying to come up with a data list validation that only
accepts either Y or N from a dropdown menu. In the Data Validation
window I enter Allow: List and Source: Y,N with Ignore blank unchecked
under the Settings tab. For the Error Alert I checked Show error alert
.... and entered text for Title: and Error message:.

For some reason that will allow the selection of Y and N and any other
text triggers the error message. However, if I have the cell selected
and hit the Delete button it will let me delete the cell without a
warning.

How can I get this to work properly?

Thanks!
Joe

If the user enters an invalid value, they'll see an error message. If
'Ignore blanks' is unchecked, and they click Retry, then press the
Delete key, they'll see another error message, because the blank cell
won't be allowed as a replacement.

However, Data Validation allows users to use the Delete key to clear a
cell in other circumstances.

Joe HM wrote:
> Hello -
>
> I have been trying to come up with a data list validation that only
> accepts either Y or N from a dropdown menu. In the Data Validation
> window I enter Allow: List and Source: Y,N with Ignore blank unchecked
> under the Settings tab. For the Error Alert I checked Show error alert
> ... and entered text for Title: and Error message:.
>
> For some reason that will allow the selection of Y and N and any other
> text triggers the error message. However, if I have the cell selected
> and hit the Delete button it will let me delete the cell without a
> warning.
>
> How can I get this to work properly?
>
> Thanks!
> Joe
>


--
Debra Dalgleish
Excel FAQ, Tips & Book List
http://www.contextures.com/tiptech.html

Joe,
this IS working properly. Data validation must allow for empty cells,
otherwise every cell would have to be populated with "valid" data
before the validation is applied. If not, you would receive a lot of
error messages when you apply validation. What you should look at if
you wish to stop users deleting entries from your sheet is the
WorkSheet_Change event in VBA.
Hth,
OJ

Hello -

Thanks to both of you! Makes sense and I think I'll use the
Worksheet_Change() event.

Joe