At the start set

Application.EnableEvents = False

and reset to true at the end.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Ed" wrote in message
...
I'm playing with getting a macro to fire every time a certain cell is
triggered. I'm using the Worksheet_SelectionChange event. Using an If
statement, I can get the macro to fire if a certain cell is selected.
BUT -
the macro selects other cells, which interrupts the running macro to
re-evaluate the SelectionChange event! Is there a way to stop this?

Ed

I'm playing with getting a macro to fire every time a certain cell is
triggered. I'm using the Worksheet_SelectionChange event. Using an If
statement, I can get the macro to fire if a certain cell is selected. BUT -
the macro selects other cells, which interrupts the running macro to
re-evaluate the SelectionChange event! Is there a way to stop this?

Ed

Hi Ed,

I'm playing with getting a macro to fire every time a certain cell is
triggered. I'm using the Worksheet_SelectionChange event. Using an If
statement, I can get the macro to fire if a certain cell is selected. BUT -
the macro selects other cells, which interrupts the running macro to
re-evaluate the SelectionChange event! Is there a way to stop this?


Like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static bStopMe As Boolean
If bStopMe Then Exit Sub
bStopMe = True
'your selecting code goes here
bStopMe = False
End Sub

But it is hardly ever needed to select cells to accomplish things in VBA.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

add
Application.EnableEvents = False
before you procedure is called, then add
Application.EnableEvents = True

after it returns...for example

If Target.Address = $G$5 Then
Application.EnableEvents = False
Call MyProc
Application.EnableEvents = True
End If

Patrick Molloy
Microsoft Excel MVP

"Ed" wrote:

I'm playing with getting a macro to fire every time a certain cell is
triggered. I'm using the Worksheet_SelectionChange event. Using an If
statement, I can get the macro to fire if a certain cell is selected. BUT -
the macro selects other cells, which interrupts the running macro to
re-evaluate the SelectionChange event! Is there a way to stop this?

Ed

Hi Bob,

At the start set

Application.EnableEvents = False

and reset to true at the end.


I am not too fond of this method, since it disables all events and
gives you little control. And when the code falls over, all events
remain disabled and things stop working, even in other VBA projects
like addins that may rely on events.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

In an empty sheet put a CommandButton1 and in the Sheet Module put this
code.

Uncomment Method 1. Now go back to the sheet and click CommandButton1 and
then click in Cell D3
This method select E3 and assigns the value 4 to the selection, then
reselects D3. In xl2002, it generated 503 calls to the SelectionChange
Event. You can then comment out method 1 and uncomment method 2 and so
forth. Method 2 doesn't reselect D3, so it only generates two calls (but
ends with E3 selected). Method1 updates E3 without selecting it and only
generates the initial call. It ends with D3 still selected. This is what
Jan means by not having to select a cell to work with it.


Dim bCnt As Long

Private Sub CommandButton1_Click()
bCnt = 0
Range("M1") = 0
End Sub

' Method 1: 503 Calls
'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
'If Target.Count 1 Then Exit Sub
'bCnt = bCnt + 1
'If Target.Address = "$D$3" Then
' Range("E3").Select
' Selection.Value = 4
' Target.Select
'End If
'Range("M1").Value = bCnt
'End Sub

'Method 2: 2 Calls
'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
'If Target.Count 1 Then Exit Sub
'bCnt = bCnt + 1
'If Target.Address = "$D$3" Then
' Range("E3").Select
' Selection.Value = 4
'End If
'Range("M1").Value = bCnt
'End Sub

' Method 3: 1 Call
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'If Target.Count 1 Then Exit Sub
'bCnt = bCnt + 1
'If Target.Address = "$D$3" Then
' Range("E3").Value = 4
'End If
'Range("M1").Value = bCnt
'End Sub

--
Regards,
Tom Ogilvy

"Jan Karel Pieterse" wrote in message
...
Hi Ed,

I'm playing with getting a macro to fire every time a certain cell is
triggered. I'm using the Worksheet_SelectionChange event. Using an If
statement, I can get the macro to fire if a certain cell is selected.
BUT -
the macro selects other cells, which interrupts the running macro to
re-evaluate the SelectionChange event! Is there a way to stop this?


Like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static bStopMe As Boolean
If bStopMe Then Exit Sub
bStopMe = True
'your selecting code goes here
bStopMe = False
End Sub

But it is hardly ever needed to select cells to accomplish things in VBA.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Thanks for the example. I'll see if I can revise my second macro.

Ed

"Tom Ogilvy" wrote in message
...
In an empty sheet put a CommandButton1 and in the Sheet Module put this
code.

Uncomment Method 1. Now go back to the sheet and click CommandButton1 and
then click in Cell D3
This method select E3 and assigns the value 4 to the selection, then
reselects D3. In xl2002, it generated 503 calls to the SelectionChange
Event. You can then comment out method 1 and uncomment method 2 and so
forth. Method 2 doesn't reselect D3, so it only generates two calls (but
ends with E3 selected). Method1 updates E3 without selecting it and only
generates the initial call. It ends with D3 still selected. This is what
Jan means by not having to select a cell to work with it.


Dim bCnt As Long

Private Sub CommandButton1_Click()
bCnt = 0
Range("M1") = 0
End Sub

' Method 1: 503 Calls
'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
'If Target.Count 1 Then Exit Sub
'bCnt = bCnt + 1
'If Target.Address = "$D$3" Then
' Range("E3").Select
' Selection.Value = 4
' Target.Select
'End If
'Range("M1").Value = bCnt
'End Sub

'Method 2: 2 Calls
'Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'
'If Target.Count 1 Then Exit Sub
'bCnt = bCnt + 1
'If Target.Address = "$D$3" Then
' Range("E3").Select
' Selection.Value = 4
'End If
'Range("M1").Value = bCnt
'End Sub

' Method 3: 1 Call
Private Sub Worksheet_SelectionChange(ByVal Target As Range)

'If Target.Count 1 Then Exit Sub
'bCnt = bCnt + 1
'If Target.Address = "$D$3" Then
' Range("E3").Value = 4
'End If
'Range("M1").Value = bCnt
'End Sub

--
Regards,
Tom Ogilvy

"Jan Karel Pieterse" wrote in message
...
Hi Ed,

I'm playing with getting a macro to fire every time a certain cell is
triggered. I'm using the Worksheet_SelectionChange event. Using an
If
statement, I can get the macro to fire if a certain cell is selected.
BUT -
the macro selects other cells, which interrupts the running macro to
re-evaluate the SelectionChange event! Is there a way to stop this?


Like this:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Static bStopMe As Boolean
If bStopMe Then Exit Sub
bStopMe = True
'your selecting code goes here
bStopMe = False
End Sub

But it is hardly ever needed to select cells to accomplish things in
VBA.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

The most obvious suggestion: Don't select E3 -- a la Tom's method 3.

However, even that will trigger the _Change event and/or the _Calculate
event. If this workbook (or some other open workbook or loaded add-in)
has an event procedure for those events, they will be executed.

In such cases, I strongly endorse a variant of the EnableEvents
approach. However, it is a persistent property. Consequently, you
*must* -- come hell or high water *must* -- enable events again.

My standard approach is:

'...
Application.EnableEvents=false
on error goto ErrXIT
'...
ErrXIT:
Application.EnableEvents=true
end sub

The static variable approach is useful in those cases where XL/VBA do
not disable certain types of events, typically in conjunction with MS
Forms. However, other than in specific instances it is a pain to
implement and impossible to enforce in a true multiple-client
environment.

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
I'm playing with getting a macro to fire every time a certain cell is
triggered. I'm using the Worksheet_SelectionChange event. Using an If
statement, I can get the macro to fire if a certain cell is selected. BUT -
the macro selects other cells, which interrupts the running macro to
re-evaluate the SelectionChange event! Is there a way to stop this?

Ed

Jan,

Take your point, but judicious use of Error handling can make sure it gets
reset. I always use code like

On Error GoTo ws_exit:
Application.EnableEvents = False
If Not Intersect(Target, Me.Range("A1:H10")) Is Nothing Then
With Target
'do your stuff
End With
End If

ws_exit:
Application.EnableEvents = True

which seems to trap the situation (of course, if I stop it in debug mode it
causes problems, but that would be my problem :-)).

What other way could you manage it? If you create your own control variable,
as you have to with form events, I don't see that differs in any material
way.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Jan Karel Pieterse" wrote in message
...
Hi Bob,

At the start set

Application.EnableEvents = False

and reset to true at the end.


I am not too fond of this method, since it disables all events and
gives you little control. And when the code falls over, all events
remain disabled and things stop working, even in other VBA projects
like addins that may rely on events.

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

apologise for treading in while experts discsuss.

these three messages which were downloaded in my computer today do not
contain all the messages. then I went to google
search tool in excel and read all the messages. I have a slightly different
more complicated problem I have an event procedure in <thisworkbook
Workbook_SheetSelectionChange
sometimes when I operate any other procedure in the standard module it
automatically fires this workbook event procedure. like any non expert I
place the enableevents line false in the beginning and true at the end of
this workbook event procedure . after I finish the standard procedure I
convert these two enable events lines into comments. But it is painful and I
have to remember everytime. I shall see whether I can adapt any of the
solutions given by the experts. any specific solutions will be appreciated.


Jan Karel Pieterse wrote in message
...
Hi Bob,

I was also thinking about the variable approach which I noticed in your
other response. That approach can fail just as easily, but of course
events
only get disabled if they use that variable, other events continue ok.
My
thoughts went along the lines, is it better to be wrong some of the time
(when the variable gets knocked out), or to be wrong all of the time (if
EnableEvents get knocked out). Couldn't come to a conclusion, as the
famous
phrase '... it depends ...' kicks in, so we have to adopt a solution to
suit
(as ever ;-)).


Agreed. Another advantage I see in using a variable is when one clicks End
on
an error message, the variable gets reset to false automatically and the
event
works once more.

But as stated in this thread more than once, the only true solution is to
use
proper error handling in the subs concerned.

Whether one uses a (static or global) variable to prevent local (or even
projectwide) looping or EnableEvents to prevent global looping depends on
the
situation.
Of course to be completely foolproof, I guess it is the latter. One never
knows what other code is "active" besides ones own application.

So: Implement Error handling!!!

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

If you expect the event procedures to be triggered during normal
operations, I would *strongly* recommend that you not disable raising
events during testing. Otherwise, your testing is far from exhaustive!
;-)

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article , $$$$vram26@vsnl
says...
apologise for treading in while experts discsuss.

these three messages which were downloaded in my computer today do not
contain all the messages. then I went to google
search tool in excel and read all the messages. I have a slightly different
more complicated problem I have an event procedure in <thisworkbook
Workbook_SheetSelectionChange
sometimes when I operate any other procedure in the standard module it
automatically fires this workbook event procedure. like any non expert I
place the enableevents line false in the beginning and true at the end of
this workbook event procedure . after I finish the standard procedure I
convert these two enable events lines into comments. But it is painful and I
have to remember everytime. I shall see whether I can adapt any of the
solutions given by the experts. any specific solutions will be appreciated.


Jan Karel Pieterse wrote in message
...
Hi Bob,

I was also thinking about the variable approach which I noticed in your
other response. That approach can fail just as easily, but of course
events
only get disabled if they use that variable, other events continue ok.
My
thoughts went along the lines, is it better to be wrong some of the time
(when the variable gets knocked out), or to be wrong all of the time (if
EnableEvents get knocked out). Couldn't come to a conclusion, as the
famous
phrase '... it depends ...' kicks in, so we have to adopt a solution to
suit
(as ever ;-)).


Agreed. Another advantage I see in using a variable is when one clicks End
on
an error message, the variable gets reset to false automatically and the
event
works once more.

But as stated in this thread more than once, the only true solution is to
use
proper error handling in the subs concerned.

Whether one uses a (static or global) variable to prevent local (or even
projectwide) looping or EnableEvents to prevent global looping depends on
the
situation.
Of course to be completely foolproof, I guess it is the latter. One never
knows what other code is "active" besides ones own application.

So: Implement Error handling!!!

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

I think the important thing is to ensure that events get re-enabled,
whatever happens in the code. This is what I meant when I referred to the
'... judicious use of error handling ...'. Along these lines,

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
'your code

ws_exit:
Application.EnableEvents = True
End Sub

As Tushar says, I would avoid commenting out the event statements. If you
want/need to disable these events when testing then you could use a debug
flag (which you set in the project properties) and use conditional
compilation, something like

#IF fDebug then
Application.EnableEvents = False
#End If
Range("A1"),Select
Application.EnableEvents = True

If you set fDebug = True for the project, the SelectionChange event does
not fire. Again you have to remember to change the flag before live release
though.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"R.VENKATARAMAN" &&& wrote in message
...
apologise for treading in while experts discsuss.

these three messages which were downloaded in my computer today do not
contain all the messages. then I went to google
search tool in excel and read all the messages. I have a slightly
different
more complicated problem I have an event procedure in <thisworkbook
Workbook_SheetSelectionChange
sometimes when I operate any other procedure in the standard module it
automatically fires this workbook event procedure. like any non expert I
place the enableevents line false in the beginning and true at the end of
this workbook event procedure . after I finish the standard procedure I
convert these two enable events lines into comments. But it is painful and
I
have to remember everytime. I shall see whether I can adapt any of the
solutions given by the experts. any specific solutions will be
appreciated.


Jan Karel Pieterse wrote in message
...
Hi Bob,

I was also thinking about the variable approach which I noticed in
your
other response. That approach can fail just as easily, but of course
events
only get disabled if they use that variable, other events continue ok.
My
thoughts went along the lines, is it better to be wrong some of the
time
(when the variable gets knocked out), or to be wrong all of the time
(if
EnableEvents get knocked out). Couldn't come to a conclusion, as the
famous
phrase '... it depends ...' kicks in, so we have to adopt a solution
to
suit
(as ever ;-)).


Agreed. Another advantage I see in using a variable is when one clicks
End
on
an error message, the variable gets reset to false automatically and the
event
works once more.

But as stated in this thread more than once, the only true solution is
to
use
proper error handling in the subs concerned.

Whether one uses a (static or global) variable to prevent local (or even
projectwide) looping or EnableEvents to prevent global looping depends
on
the
situation.
Of course to be completely foolproof, I guess it is the latter. One
never
knows what other code is "active" besides ones own application.

So: Implement Error handling!!!

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

thanks. I shall study this to suit my needs.


Bob Phillips wrote in message
...
I think the important thing is to ensure that events get re-enabled,
whatever happens in the code. This is what I meant when I referred to the
'... judicious use of error handling ...'. Along these lines,

Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target
As Range)
On Error GoTo ws_exit:
Application.EnableEvents = False
'your code

ws_exit:
Application.EnableEvents = True
End Sub

As Tushar says, I would avoid commenting out the event statements. If you
want/need to disable these events when testing then you could use a debug
flag (which you set in the project properties) and use conditional
compilation, something like

#IF fDebug then
Application.EnableEvents = False
#End If
Range("A1"),Select
Application.EnableEvents = True

If you set fDebug = True for the project, the SelectionChange event does
not fire. Again you have to remember to change the flag before live
release
though.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"R.VENKATARAMAN" &&& wrote in message
...
apologise for treading in while experts discsuss.

these three messages which were downloaded in my computer today do not
contain all the messages. then I went to google
search tool in excel and read all the messages. I have a slightly
different
more complicated problem I have an event procedure in <thisworkbook
Workbook_SheetSelectionChange
sometimes when I operate any other procedure in the standard module it
automatically fires this workbook event procedure. like any non expert I
place the enableevents line false in the beginning and true at the end
of
this workbook event procedure . after I finish the standard procedure I
convert these two enable events lines into comments. But it is painful
and
I
have to remember everytime. I shall see whether I can adapt any of the
solutions given by the experts. any specific solutions will be
appreciated.


Jan Karel Pieterse wrote in message
...
Hi Bob,

I was also thinking about the variable approach which I noticed in
your
other response. That approach can fail just as easily, but of course
events
only get disabled if they use that variable, other events continue
ok.
My
thoughts went along the lines, is it better to be wrong some of the
time
(when the variable gets knocked out), or to be wrong all of the time
(if
EnableEvents get knocked out). Couldn't come to a conclusion, as the
famous
phrase '... it depends ...' kicks in, so we have to adopt a solution
to
suit
(as ever ;-)).


Agreed. Another advantage I see in using a variable is when one clicks
End
on
an error message, the variable gets reset to false automatically and
the
event
works once more.

But as stated in this thread more than once, the only true solution is
to
use
proper error handling in the subs concerned.

Whether one uses a (static or global) variable to prevent local (or
even
projectwide) looping or EnableEvents to prevent global looping depends
on
the
situation.
Of course to be completely foolproof, I guess it is the latter. One
never
knows what other code is "active" besides ones own application.

So: Implement Error handling!!!

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com

Jan Karel and Bob,

Interesting points. Which choice of method is a dilemma I've struggled with.
Ideally I prefer to disable/enable events, but no matter how good my error
handling there is always the possibility of some unknown interaction with
other stuff in the user's app.

Eg a calculation caused by writing to cells can fire user's udf's, even in
an unrelated workbook. If these are not correctly handled they can cause all
code to just stop (not same as "end"), without realising variables in my
code (particularly problematic in xl97). Of course the solution is to
disable calculation but that's just another thing that might not get reset
correctly. In any case would prefer not to disable calculation merely to
cater for a small possibility of such a problem.

Regards,
Peter T

"Jan Karel Pieterse" wrote in message
...
Hi Bob,

I was also thinking about the variable approach which I noticed in your
other response. That approach can fail just as easily, but of course
events
only get disabled if they use that variable, other events continue ok.
My
thoughts went along the lines, is it better to be wrong some of the time
(when the variable gets knocked out), or to be wrong all of the time (if
EnableEvents get knocked out). Couldn't come to a conclusion, as the
famous
phrase '... it depends ...' kicks in, so we have to adopt a solution to
suit
(as ever ;-)).


Agreed. Another advantage I see in using a variable is when one clicks End
on
an error message, the variable gets reset to false automatically and the
event
works once more.

But as stated in this thread more than once, the only true solution is to
use
proper error handling in the subs concerned.

Whether one uses a (static or global) variable to prevent local (or even
projectwide) looping or EnableEvents to prevent global looping depends on
the
situation.
Of course to be completely foolproof, I guess it is the latter. One never
knows what other code is "active" besides ones own application.

So: Implement Error handling!!!

Regards,

Jan Karel Pieterse
Excel MVP
www.jkp-ads.com