Stop Worksheet_SelectionChange event from firing?

[Bob Phillips]

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]

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 Karel Pieterse]

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

 

[Guest]

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

 

[Jan Karel Pieterse]

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

 

[Tom Ogilvy]

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

 

[Ed]

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

Ed

 

[Tushar Mehta]

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

 

[Bob Phillips]

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]

Hi Bob,

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

True. But in this case (newsgroup posting) it tends to be forgotten.

Regards,

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

 

[Bob Phillips]

You are right, and I should have mentioned it. I always include it in
solutions, but should also mention in informational posts .

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 ;-)).

--

HTH

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

 

[Jan Karel Pieterse]

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

 

[R.VENKATARAMAN]

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.

 

[Tushar Mehta]

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

 

[Bob Phillips]

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]

thanks. I shall study this to suit my needs.

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)


and

 

[Peter T]

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