I use the following code to disable macros, but it is to indiscriminate.

Application.EnableEvents = True

Is there any way to disable only Private Sub Worksheet_SelectionChange
events and still allow other events to happen?

One way:

Create a global variable (say "bSELECTIONCHANGE").

Then wrap your event macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If bSELECTIONCHANGE Then
'... your macro here...
End If
End Sub

Turn off the macro using

bSELECTIONCHANGE = False

and turn it back on with

bSELECTIONCHANGE = True



In article ,
"Patrick Simonds" wrote:

I use the following code to disable macros, but it is to indiscriminate.

Application.EnableEvents = True

Is there any way to disable only Private Sub Worksheet_SelectionChange
events and still allow other events to happen?

I do not know how to create a global variable or where to place it when I
do.


"JE McGimpsey" wrote in message
...
One way:

Create a global variable (say "bSELECTIONCHANGE").

Then wrap your event macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If bSELECTIONCHANGE Then
'... your macro here...
End If
End Sub

Turn off the macro using

bSELECTIONCHANGE = False

and turn it back on with

bSELECTIONCHANGE = True



In article ,
"Patrick Simonds" wrote:

I use the following code to disable macros, but it is to indiscriminate.

Application.EnableEvents = True

Is there any way to disable only Private Sub Worksheet_SelectionChange
events and still allow other events to happen?

You can put the global variable in a general module:

Option Explicit
Public bSELECTIONCHANGE As Boolean

Then for a very basic (pronounced bad) example (behind a worksheet).

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

If Target.Cells.Count 1 Then Exit Sub
If Intersect(Target, Me.Columns(1)) Is Nothing Then Exit Sub

bSELECTIONCHANGE = True
'next line changes selection, so the _selectionchange event will fire
Me.Cells(Target.Row, "E").Select
bSELECTIONCHANGE = False

End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If bSELECTIONCHANGE = True Then Exit Sub
'your regular code here
MsgBox "made it to the _selectionchange ""real"" code"
End Sub




Patrick Simonds wrote:

I do not know how to create a global variable or where to place it when I
do.

"JE McGimpsey" wrote in message
...
One way:

Create a global variable (say "bSELECTIONCHANGE").

Then wrap your event macro:

Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
If bSELECTIONCHANGE Then
'... your macro here...
End If
End Sub

Turn off the macro using

bSELECTIONCHANGE = False

and turn it back on with

bSELECTIONCHANGE = True



In article ,
"Patrick Simonds" wrote:

I use the following code to disable macros, but it is to indiscriminate.

Application.EnableEvents = True

Is there any way to disable only Private Sub Worksheet_SelectionChange
events and still allow other events to happen?

--

Dave Peterson