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

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

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

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

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

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

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

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