Worksheet Change Event problem

[Doug]

I have the following code on Sheet1: Thanks to Frank K.
--------------
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit
Sub
On Error GoTo CleanUp:
With Target
If .value then
Application.EnableEvents = False
Call DoSomething 'Private Sub on same sheet
end if
End With

CleanUp:
Application.EnableEvents = True
End Sub

-------------

Change Event fires but evaluates "A1" to be "Nothing" and
exits. But if I simply click the formula for "A1" and
hit ENTER it works just fine!

What am I doing wrong?

 

[Tom Ogilvy]

Clicking a cell does not fire the change event. Editing a cell does (which
you do by clicking in the formula and hiting enter). I suspect you are
mistaken that this fires when you click in a cell.

 

[Doug]

Tom,

You are correct -- clicking in a cell does nothing. What
I tried to say is that despite that the contents of A1
are clearly non-blank and the change event code gets
called, it evaluates the contents as blank unless, as you
pointed out, I touch the formula and hit enter.

Doug

 

[Tom Ogilvy]

the code never looks at the contents of Cell A1. It is set up to not do
anything unless the cell edited is A1. That is what this line does:

If Intersect(Target, Me.Range("A1")) Is Nothing Then Exit


This basically says "is the cell triggering the change event cell A1?"

If it is not, then exit this subroutine (do nothing).

 

[Bob Phillips]

Doug,

Unless something is changed, that code won't even be entered. The nothing
does not refer to the contents of A1, but the fact of whether the object
resulting from a union of A1 and the changed cell is nothing or not.

Even if A1 is not empty and you change B1, the code will check whether it is
A1 that was change at that time, conclude not, and exit.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Thanks for the response Bob.

I've scattered several message boxes in that code and,
when A1 has indeed changed, it enters the change event
where it is always evaluated at Nothing. A1 is a logical
determined by the contents of a list-box -- YES or NO.

In testing all this I'm calculating the value of A1 by
the contents of the list-box making A1 either True or
False.

I'm lost.



"With Target" segment where it errors out

 

[Bob Phillips]

Send me your workbook, and I will look at it for you.

bob . phillips @ tiscali . co . uk - remove the spaces

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)