Changing Cell Text During Change Event

[Guest]

I'm using Excel 2003 and writing some VBA code to validate cell contents.

I'm using the "Worksheet_Change" event to trap when a user modifies the
contents of a cell ($1:$D). If the contents are invalid I want to display a
message in another cell ($1:$C). While stepping through the code I find the
event fires fine, and using the following code my display cell populates fine:

Me.Cells(intRow, 3) = strErrorMessage

And I can see this new text on the screen. However, once that text is
applied again, the "Worksheet_Change" event fires again. The text remains in
there until the end of the first call to the event, then it clears out.

Is anyone aware of a problem modifying the contents of other cells, while in
the middle of the "Change" event of another? Anyone know a work around?

Thanks.

 

[Die_Another_Day]

Can you post your code here? The first thing I would try would be to
disable events after the first event triggers like this:

Sub Worksheet_Change(BlahBlahBlah)
Application.EnableEvents = False
'Your Code
Application.EnableEvents = True
End Sub

HTH

Die_Another_Day

 

[Guest]

I feel very silly...I guess all you need is another programmer to make you
look at your code again to see a simple syntax error.

My "ValidateCell" function was setting the value in the reporting cell...but
it was a function, whose result was populating the reporting cell... I guess
at first I thought a function would be good...but forgot halfway through that
I was using a function. Maybe I've been working on this too long.

Thanks for learning me on the "Application.EnableEvents"--that might come in
handy someday.