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

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

"Dr. Schwartz" wrote in message
...
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

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


€žDr. Schwartz€� ezt Ã*rta:

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

It also works for me. However, a note...
it doesn't make the call if cell M7 is deleted (right-click and choose delete).
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)

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.
--
Gary''s Student - gsnu200769


"Dr. Schwartz" wrote:

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

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

"Gary''s Student" wrote:

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.
--
Gary''s Student - gsnu200769


"Dr. Schwartz" wrote:

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