Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

I want to restrict users to entering either just 0 or 1 in an Excel 2007 sheet.

I use the Data > Data Validation dialog to do this, but I'm finding that it doesn't stop them from entering blanks.

What I would like is for there to be a prompt when they enter blanks, just like the one that appears when they enter any other non-blank data that is not 0 or 1.

I plan to fill the sheet with 0s before applying the validation, so there should not be a problem with erroneous data.

Also, I'm open to using VBA to fix this problem.

share|improve this question
up vote 1 down vote accepted

This change event procedure will look at any cell that has validation and display the error message from the validation. It doesn't work like validation in that it doesn't force a "retry". Also, it sticks a '1' in there if it gets deleted. You could put the old value in there, but it would take more work and it's generally something I try to avoid.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim sForm As String

    On Error Resume Next
        sForm = Target.Validation.Formula1
    On Error GoTo 0

    If Len(sForm) > 0 Then 'has validation
        If IsEmpty(Target.Value) Then 'cell was deleted
            MsgBox Target.Validation.ErrorMessage, vbOKOnly, Target.Validation.ErrorTitle
            Application.EnableEvents = False
                Target.Value = 1
            Application.EnableEvents = True
        End If
    End If

End Sub
share|improve this answer
Thanks - this worked so well that I'm using it instead of my validation. :) – Tola Odejayi Dec 24 '10 at 1:25

There should be an option in the Data Validation settings to "Ignore blank". If you disable (uncheck) the option does that not do what you desire?

Set the DV to a Whole Number between 0 and 1 and uncheck "Ignore blank"

share|improve this answer
I tried that, but it doesn't work. I find that I can still enter blank data. – Tola Odejayi Dec 23 '10 at 22:17

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .