Change Event is looping indefinitely!

[Christiane]

Iwant to write code so that if someone enters a value, hello 1225, in
cell in a certain range, a message box will be displayed with a warnin
sign.
I wrote the following code, it works but when hello 1225 is entered
the dialog is displayed, but when I clickok, it is displayed again an
again and again.

What did I do wrong?

Thanks for any help.
Christiane

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Font.ColorIndex = 5

For Each c In Worksheets("Sheet1").Range("A110")

If c.Value = "hello 1225" Then
Worksheets("Sheet1").Range("A2").Value = 10
MsgBox "Verify tank level"

End If
Next c

End Su

 

[Jake Marx]

Hi Christiane,

The Worksheet_Change event will be fired every time a cell's value changes
on that Worksheet. This includes when a value is changed via code. So when
the cell is changed, your code runs, which changes the cell's value again,
which in turn triggers the event, which runs your code again, and so on.
This will continue until Excel decides it's had enough. <g>

To avoid this, you can use a module-level Boolean variable in conjunction
with your code:

Private mbDisableEvents As Boolean

Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range

If Not mbDisableEvents Then
mbDisableEvents = True
Application.EnableEvents = False
Target.Font.ColorIndex = 5

For Each c In Worksheets("Sheet1").Range("A110")

If c.Value = "hello 1225" Then
Worksheets("Sheet1").Range("A2").Value = 10
MsgBox "Verify tank level"
End If
Next c
Application.EnableEvents = True
mbDisableEvents = False
End If
End Sub

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Frank Kabel]

Hi
add the line
application.enableevents = False
at the beginning of your code and the line
application.enableevents = True
at the end of your macro

 

[Tom Ogilvy]

Private Sub Worksheet_Change(ByVal Target As Range)
Target.Font.ColorIndex = 5
On Error goto ErrHandler
For Each c In Worksheets("Sheet1").Range("A110")

If c.Value = "hello 1225" Then
Application.EnableEvents = False
Worksheets("Sheet1").Range("A2").Value = 10
MsgBox "Verify tank level"

End If
Next c
ErrHandler:
Application.EnableEvents = True
End Sub

It will appear for as many times as you have cells in A110 that contain
hello 1225

 

[Frank Kabel]

Hi Jake
what is the reason for you mbDisableEvents variable. Disabling the
application events should be enough

 

[Squid]

You need to turn off enable events or it will
continuously call itself.

Private Sub Worksheet_Change(ByVal Target As Range)

Application.EnableEvents = false

Target.Font.ColorIndex = 5

For Each c In Worksheets("Sheet1").Range("A110")

If c.Value = "hello 1225" Then
Worksheets("Sheet1").Range("A2").Value = 10
MsgBox "Verify tank level"

End If
Next c

Application.EnableEvents = true

 

[Christiane]

Thank you all.

I disabled the application events and it works.

But as Tom noted, I see the message box diplayed twice if "hello 1225
is entered twice in the range.

How would I change the code to display the message box only once o
entry, even if the value already exists in the range.

Thanks,
Christian

 

[Jake Marx]

Frank,

Hi Jake
what is the reason for you mbDisableEvents variable. Disabling the
application events should be enough

Quite right - I was obviously confused between Excel-triggered events and
other ActiveX events, which can't be disabled via EnableEvents. Maybe I
should go back to sleep....

Thanks for the correction, and sorry for any confusion I caused.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]

 

[Tom Ogilvy]

On check the cell that triggers the change event: Target