Validation No Blanks (XL2K)

Topic

New Reply

I am trying to validate a lookup table so that it has to have numbers in it. Zero values are acceptable but blanks are not as they break formulas further on in the spreadsheet.

I have tried setting the validation to Number and Greater than or Equal to 0 and deselecting the ignore blanks but it still allows the blanks.

Many thanks for any pointers

Peter

Reply | Quote

Replies

I don’t think that data valadation can be used for this. The Code below, placed in the worksheet change event routine, will insure that cells A1:A100 on that worksheet can only be changed to a numeric value.

Private Sub Worksheet_Change(ByVal Target As Range)
Dim oCell As Range
    If Intersect(Target, ActiveSheet.Range("A1:A100")) Is Nothing Then Exit Sub
    For Each oCell In Intersect(Target, ActiveSheet.Range("A1:A100"))
        If (Not IsNumeric(oCell.Value)) Or oCell.Value = "" Then
            Intersect(Target, ActiveSheet.Range("A1:A100")).Select
            MsgBox "Cells must contain numeric value."
            Exit Sub
        End If
    Next oCell
End Sub

Reply | Quote

Thanks for the code Legare.
On further investigation it was not the blanks that was breaking my formulas anyway Still it seems strange that “Ignore Blanks” seems to make no difference when specifying numbers only!

Peter

Reply | Quote

When you open up the validation on the right hand side there is a check box that says “Ignore Blanks”. The default is for it to be checked, uncheck it and see what happens.

Stats

Reply | Quote