pop up box

[Guest]

I would like a box to pop up saying NEW RECORD.

In column F if the cell is the minimun number then say NEW RECORD
For example F1 has 200
F2 has 199
F3 has 144 so now If I put 140 in F4 a box appears
sayin new record
but if I put 150 in cell A4 then nothing
is this possible?
Thanks

 

[Guest]

Put this in sheets code module

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
Application.EnableEvents = False
x = Target: Target = ""
If x < Application.WorksheetFunction.Min(Range("F:F")) Then MsgBox ("NEW
RECORD")
Target = x
Application.EnableEvents = True
End Sub


"delete automatically" skrev:

 

[Guest]

When I copied and paste the formula the If x <
Application.WorksheetFunction.Min(Range("F:F")) Then MsgBox ("NEW RECORD")
was red. Then when I tried it gave me a compile error and Private Sub
Worksheet_Change(ByVal Target As Range) was highlited yellow.
Any ideas?

 

[Gord Dibben]

You have been hit by wordwrap in the posting that's why the red text appears

Try this revision pasted with the line continuation mark "_" added.


Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
Application.EnableEvents = False
x = Target: Target = ""
If x < Application.WorksheetFunction.Min(Range("F:F")) _
Then MsgBox ("NEW Record ")
Target = x
Application.EnableEvents = True
End Sub

BTW...........it is not a formula, it is event code.


Gord Dibben MS Excel MVP

 

[Guest]

I ges it's the usual formatting problems in here try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
Application.EnableEvents = False
x = Target: Target = ""
If x < Application.WorksheetFunction.Min(Range("F:F")) _
Then MsgBox ("NEW RECORD")
Target = x
Application.EnableEvents = True
End Sub


"delete automatically" skrev:

 

[Guest]

Thank you, that worked perfectly

I ges it's the usual formatting problems in here try:

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
Application.EnableEvents = False
x = Target: Target = ""
If x < Application.WorksheetFunction.Min(Range("F:F")) _
Then MsgBox ("NEW RECORD")
Target = x
Application.EnableEvents = True
End Sub


"delete automatically" skrev:

 

[T. Valko]

This can be done using data validation.

Suppose the range of interest is F1:F10
Select the range F1:F10
Goto Data>Validation
Allow: Custom
Formula: =F1<>MIN(F$1:F$10)
Click the Error Alert tab
Check: Show error alert......
Style: Information
Type in a message like: New Record
OK out