data validation

[MartinL]

Good morning

I have a cell which requires a user to put an entry of between 0 and 1 million
so I used the validation
Allow: Whole Number
Data: Between
Minimum: 0
Maximum: 1000000

Ignore Blank: Unchecked

I have a message which tells the user the acceptable range in case they try to enter a non numeric or numbers outside of this range.

However! Blanks are being accepted can this validation enforce a number between 0 and 1 million,rather than allowing blanks, the error works if a letter, negative, decimal or exceeds 1 million

 

[RoryA]

Data validation only validates data entry, not the lack of an entry.

 

[MartinL]

Awesome - that is the lateral thinking I was missing

How stupid do I feel right now
Thanks for not taking the .............

 

[RoryA]

no problem.

You'd have to do something like use a BeforeSave event if you need to validate before saving.

 

[MartinL]

no problem.

You'd have to do something like use a BeforeSave event if you need to validate before saving.

I tried this but I'm obviously missing something
it is supposed to be checking the value of 5 (merged) cells
and replacing the value with 0 if found to be blank. The idea is it runs the vba when changing the cell, the sheet will open with values in it

<code>Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$19:$F$19" Then
    If Range("D19").Value = "" Then
        Range("D19").Value = 0
ElseIf Target.Address = "$H$19:$J$19" Then
    If Range("H19").Value = "" Then
        Range("H19").Value = 0
ElseIf Target.Address = "$L$19:$N$19" Then
    If Range("L19").Value = "" Then
        Range("L19").Value = 0
ElseIf Target.Address = "$P$19:$R$19" Then
    If Range("P19").Value = "" Then
        Range("P19").Value = 0
ElseIf Target.Address = "$T$19:$V$19" Then
    If Range("T19").Value = "" Then
        Range("T19").Value = 0
End If
End Sub</code>

 

[MartinL]

no problem.

You'd have to do something like use a BeforeSave event if you need to validate before saving.

I did this
it is supposed to be checking the value of 5 (merged) cells
and replacing the value with 0 if found to be blank. The idea is it runs the vba when changing the cell, the sheet will open with values in it

<code>Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$19:$F$19" Then
    If Range("D19").Value = "" Then Range("D19").Value = 0
ElseIf Target.Address = "$H$19:$J$19" Then
    If Range("H19").Value = "" Then Range("H19").Value = 0
ElseIf Target.Address = "$L$19:$N$19" Then
    If Range("L19").Value = "" Then Range("L19").Value = 0
ElseIf Target.Address = "$T$19:$V$19" Then
    If Range("T19").Value = "" Then Range("T19").Value = 0
End If
End Sub</code>