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.


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
  • 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"

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.