Thank you very much. No problem now ^^
Actually, what is Application.EnableEvents = False and True means?
Why Application.EnableEvents = False can still take effect to the Change
event?
"KL"
...
How about something like this:
Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
If Range("BJ1") <= Range("BI1") And Range("BK1") = 0 Then
MsgBox (Range("BH1").Value & " reach the minimum level")
Range("BK1") = 1
ElseIf Range("BJ1") Range("BI1") Then
Range("BK1") = 0
End If
Application.EnableEvents = True
End Sub
also, you may want to introduce a condition for the code depending on the
range that is changed. For example: if the range that is going to affect
the result is [A1:D10] you could do something like this:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A1:D10")) Is Nothing Then Exit Sub
Application.EnableEvents = False
If Range("BJ1") <= Range("BI1") And Range("BK1") = 0 Then
MsgBox (Range("BH1").Value & " reach the minimum level")
Range("BK1") = 1
ElseIf Range("BJ1") Range("BI1") Then
Range("BK1") = 0
End If
Application.EnableEvents = True
End Sub
--
KL
[MVP - Microsoft Excel]
RU: http://www.mvps.ru/Program/Default.aspx
ES: http://mvp.support.microsoft.com/?LN=es-es
EN: http://mvp.support.microsoft.com/?LN=en-us
Profile:
https://mvp.support.microsoft.com/pr...A-9E6C73C09A36
"jimmy" wrote in message
...
Hi all,
The function:
Private Sub Worksheet_Change(ByVal Target As Range)
If Range("BJ1") <= Range("BI1") And Range("BK1") = 0 Then
MsgBox (Range("BH1").Value & " reach the minimum level")
Range("BK1") = 1
End If
If Range("BJ1") Range("BI1") Then Range("BK1") = 0
End Sub
BH1 is pointed to a cell contains text, BI1 and BJ1 are pointed to
different cells which do simple +/-. The above function is just for
testing, there will be about 50 items that I will use array and for loop
if the testing is ok.
To prevent the message prompt so many times, I have set the flag(BK1)
keep on 1 until "BJ1" "BI1" again. But I don't know why everytime a
increment in BJ1, the application is hang for about 10 seconds. Please
help to point out my mistake, thank you.