Worksheet_change event assistance

[Dr. Schwartz]

Please help me out!

I have this piece of code:

Public Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("M7")) Is Nothing Then
FillOutReport
End If
End Sub

I want to change it so that FillOutReport also is called when the M7 is
cleared - meaning any change in the cell should call FillOutReport.

Anyone able to solve this?

Thanks
The Doctor

 

[Stefi]

I tried your code in XL2003, and FillOutReport was triggered by clearing M7.
What is the problem?
Regards,
Stefi


„Dr. Schwartz†ezt írta:

 

[Peter T]

It looks fine to me. Namely, if M7 has just changed the Intersect will
return a range hence passing the If test.

Is the Change event code in the correct sheet module and are events enabled.

Regards,
Peter T

 

[Jim Cone]

It also works for me. However, a note...
it doesn't make the call if cell M7 is deleted (right-click and choose delete).

 

[Gary''s Student]

The event code should be triggered by either setting M7 or clearing M7. I
would look very carefully at FillOutReport and any other macros to insure
they are not causing Application.EnableEvents to become false. This would
cause your symptoms. Also as a general preventative measure, use:

Application.EnableEvents=FLASE
Call FillOutReport
Application.EnableEvents=TRUE

If your condition does not improve, update the post.

 

[Dr. Schwartz]

How very strange! I tried it out once more (in addition to the several 100 i
did before I wrote this post) and now it works...

Sorry to bother you all and thank you for spending time on this.

To Garys student: enabling and disabling the event while running a sub is
very useful and saves time. However this could not have caused the problem as
I was not aware of this functionality.

Thanks again
The Doctor