Validation Message

Posted by Qroozn on September 20, 2001 3:51 PM

I want an on error allert(validation) message to say " the value must be between "a" and "b"", (where a and b are values derived from cell references.) when an entered value falls outside the limitations "a" and "b".

Please help

validation does not let your allert message reference cell values.

Posted by Robb on September 21, 2001 4:11 AM

Try something like this in the Change event of the Worksheet.

In this instance, it is for Sheet1, with B9 tested against values in A4 and A5.

In case - just right click on the sheet1 tab and select "View Code"
Copy/paste this into the code page that appears.
To try it out, put the lower value in A1 and a higher one in A5 then enter something
outside those figures in B9 - it should result in the sort of message you are after.

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
With Worksheets("Sheet1")
If Not Application.Intersect(Target, .[b9]) Is Nothing Then
If Target &LT; .[a4] Or Target > .[a5] Then
MsgBox "Value must be between " & .[a4] & " and " & .[a5]
Else
End If
End If
End With
End Sub

Any help?

Regards

Posted by lenze on September 21, 2001 6:18 AM

You can do this without code using the Data Validation Custom option. The trick is to use Named Ranges instead of cell references. Name the cell where the minimum value is located something like "LOW" and the maximum value cell "HIGH" If the cell you want to validate is $A$6, then the formula in the custom field would be =AND($A$6>LOW,$A$^<HIGH)

Posted by lenze on September 21, 2001 6:21 AM

=AND($A$6>LOW,$A$6>HIGH)

Posted by Lenze on September 21, 2001 6:30 AM

Hard time typing this morning

Should be <HIGH

Posted by Qroozn on September 23, 2001 3:59 PM

I've added it to my script, but it does absolutely nothing. I run excel 97. Does this change anything?

I have tried on change events before AND THEY NEVER DO ANYTHING.

TY for your assistance

Posted by Qroozn on September 23, 2001 5:33 PM

Ok. I got it going. my error.
Thanks for the code. The prob now is that whenever i click on b9 then the error message pops up. can i do it so that the macro only runs after pressing enter on that cell. ( i only want the error to come up if the value they enter is outside the range. instead it is prompting everytime they go to enter a value.

Posted by Robb on September 24, 2001 4:00 AM

If you're using the Data Validation method, go to Data/Validation and select
the Input Message tab - unselect "Show input message when cell is selected"

If you're using the Sheet2 code, make sure you left the code attached to
the Change event - this ensures the message box presents only when a value
outside the range is entered.

Any help?

Regards

Ok. I got it going. my error.