data validation

[sjoerd.bosch]

I need a data validation restriction for a cell
problem is that the cell in which I need the restriction contains a formula and I guess that is the reason why my data validation entry does not work.
For example: cell d4 / cell d5 = result in cell d6 The result in cell d6 should be not less than 12.0000 (decimals must be allowed)
A VBA code is also possible, but I thought this would be something simple in data validation

 

[sjoerd.bosch]

Sorry - but

How about

=IF(D4/D5<12, "The current value is " & D4/D5 & " which is less than 12", D4/D5)

that does allow other input values

 

[kevin9999]

I have d

Done that - but I prefer to display a message to the users, as they just ignore the colour change

The code in post #6 will both display a message and remove the invalid entry.

 

[sjoerd.bosch]

does not

Sorry - but


that does not allow other input values

 

[sjoerd.bosch]

I understand, but in some cases a lower value should be possible
Therefore the value should not be removed
Just need a message displayed
Was hoping a simple data validation would work, but seems not to be possible

The code in post #6 will both display a message and remove the invalid entry.

 

[kevin9999]

does not

Not sure who you're replying to there, but if it's me, I can assure you that the code does work. Are you familiar with worksheet change code? Right click the tab name of the sheet in question, select View Code, and copy the code into the window on the right of screen.

 

[kevin9999]

I understand, but in some cases a lower value should be possible
Therefore the value should not be removed
Just need a message displayed
Was hoping a simple data validation would work, but seems not to be possible

OK, just remove the line Target.ClearContents.

 

[BigBeachBananas]

Sorry - but


that does allow other input values

I don't understand what you're trying to achieve. If D4/D5 <12, then show a warning message in D6, but you don't want people to fix their input in D4 or D5?

 

[sjoerd.bosch]

Thanks Kevin
That code does work fine. Appreciated!!!

Not sure who you're replying to there, but if it's me, I can assure you that the code does work. Are you familiar with worksheet change code? Right click the tab name of the sheet in question, select View Code, and copy the code into the window on the right of screen.

 

[kevin9999]

G

Thanks Kevin
That code does work fine. Appreciated!!!

Glad we could help, and thanks for the feedback

 

[sjoerd.bosch]

Kevin
I ran into another issue when trying to paste the code into a sheet were data and a vba code was already inserted
the cells for which I need the data have also changed - and I changed that in the code.
F20/F22 = F24
(270/24 = 11.3 and is less than 12)
but when running I get an ambitigeous name error
The vba codes required in this page is below. The name of the sheet is Daily Input


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge = 1 And Not Intersect(Range("F15:F16"), Target) Is Nothing Then
On Error GoTo Escape
Application.EnableEvents = False
Target = Trim(VBA.UCase(Target.Value2))
Target.Replace ",", "."

If Target.Address = "$F$15" Then
If Not Target Like "##-##.# [N]" And Not Target Like "##-##.# " Then
MsgBox "Latitude must be entered in the format 00-00.0 N (or S)"
Target = "00-00.0 N"
GoTo Continue
End If
If Left(Target, 2) > 90 Then
MsgBox "Maximum allowable values are: 90-99.9"
Target = "00-00.0 N"
GoTo Continue
End If
End If

If Target.Address = "$F$16" Then
If Not Target Like "###-##.# [E]" And Not Target Like "###-##.# [W]" Then
MsgBox "Longitude must be entered in the format 000-00.0 E (or W)"
Target = "000-00.0 E"
GoTo Continue
End If
If Left(Target, 3) > 180 Then
MsgBox "Maximum allowable values are: 180-99.9"
Target = "000-00.0 E"
GoTo Continue
End If
End If
End If

Continue:
Application.EnableEvents = True
Exit Sub
Escape:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Continue
End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.CountLarge = 1 And Not Intersect(Range("F20:F22"), Target) Is Nothing Then
On Error GoTo Escape
Application.EnableEvents = False
If Range("F24") < 12 Then
MsgBox "Formula results in D5 less than 12 - please re-enter"
Target.ClearContents
Target.Select
End If
End If
Continue:
Application.EnableEvents = True
Exit Sub
Escape:
MsgBox "Error " & Err.Number & ": " & Err.Description
Resume Continue
End Sub

Another option is a worksheet change event. First, change your formula to:

=IFERROR(D4/D5,0)

And put this code on the sheet code area of the sheet with the formula:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Cells.CountLarge = 1 And Not Intersect(Range("D4:D5"), Target) Is Nothing Then
    On Error GoTo Escape
    Application.EnableEvents = False
        If Range("D6") < 12 Then
            MsgBox "Formula results in D5 less than 12 - please re-enter"
            Target.ClearContents
            Target.Select
        End If
    End If
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub