Message box repeats itself

[Christiane]

The problem I have now is that I want to display a message box whe
value "solvent 1225" is entered in a range.

It works, but if that value is already in the range, I get the messag
box twice, if the data is there 3 times, the message box appears
times.

I want the message box to appear only once, even though the same valu
is in the range.

Can anyone help me?
Christiane


Private Sub Worksheet_Change(ByVal Target As Range)


Application.EnableEvents = False


For Each c In Worksheets("Sheet1").Range("A:A")


If c.Value = "solvent 1225" Then
MsgBox "DO NOT ORDER MORE THAN 50,000!", vbCritical
"Warning!"

End If

Next c

Application.EnableEvents = True

End Su

 

[kkknie]

I believe this will do it:

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
For Each c In Worksheets("Sheet1").Range("A:A")
If c.Value = "solvent 1225" Then
MsgBox "DO NOT ORDER MORE THAN 50,000!", vbCritical, "Warning!"
EXIT FO
End If
Next c
Application.EnableEvents = True
End Su

 

[Christiane]

It worked!!

Thanks a lot kkknie

 

[Christiane]

But if there is already a solvent 1225 value in the range, and I ente
any value, like 10, the message box still appears.

I want the message box to appear only once when a new value of solven
1225 is entered in the range.

Thanks for any help

 

[kkknie]

OK... Didn't really look at your code too deeply, but I think this i
what you want. You also should probably add something so it doesn'
work on all cell changes (just the order quantity).

My code assumes that B1 is the quantity field.


Code
-------------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Address = "$B$1" Then

Application.EnableEvents = False
For Each c In Worksheets("Sheet1").Range("A:A")
If c.Value = "solvent 1225" and Target.Value > 50000 Then
MsgBox "DO NOT ORDER MORE THAN 50,000!", vbCritical, "Warning!"
Exit For
End If
Next c
Application.EnableEvents = True

End If

End Su
-------------------

 

[Frank Kabel]

Hi
not fully tested but try:

Dim status
Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = False
For Each c In Worksheets("Sheet1").Range("A:A")
If c.Value = "solvent 1225" Then
if not status then
MsgBox "DO NOT ORDER MORE THAN 50,000!", vbCritical, "Warning!"
status = True
EXIT FOR
end if
End If
Next c
status = false
Application.EnableEvents = True
End Sub

 

[Tom Ogilvy]

Just check the cell that was changed and triggered the event.

Private Sub Worksheet_Change(ByVal Target As Range)
if Target.count >1 then exit sub
if Target.column = 1 then
if lcase(target.value) = "solvent 1225") then
MsgBox "DO NOT ORDER MORE THAN 50,000!", vbCritical, "Warning!"
end if
end if
End Sub

 

[Harald Staff]

Hi

Not tested at all, but I think it should read Exit Sub instead of Exit For
to avoid the status reset at the end.

HTH. Best wishes Harald

 

[LarryN]

Put the code Exit For after the msgbox.

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False

For Each c In Worksheets("Sheet1").Range("A:A")

If c.Value = "solvent 1225" Then
MsgBox "DO NOT ORDER MORE THAN 50,000!", vbCritical,
"Warning!"
EXIT FOR
End If

Next c

Application.EnableEvents = True

End Sub

 

[Christiane]

Thanks Tom it works.

I didn't know how to code to check just for data entered in a cell.

Christiane

Thanks everyone!