Disable running of SelectionChange macro when in another macro?

[Tonso]

Using Excel 2003 I have a macro in a module that automatically deletes
cells. The problem is that as it selects these cells, it activates a
SelectionChange macro. The SelectionChange macro then slows this other
macro down tremendously, even though I have screenupdating off in the
module macro. Is there a way to run the module macro without it
invoking the SelectionChange macro in the sheet?

Thanks

 

[Don Guillett]

As ALWAYS post your code for comments. There is rarely a need to select

 

[Tonso]

I have selected because I had some problems with merged rows (Columns
A,B,C,D)

Module code:

Sub ClearSequence()
Dim r As Long
Dim Start As Long
'test
If ActiveCell.Row < 14 Then Exit Sub
Answer = MsgBox("Are you sure that you want to CLEAR this MOST
Sequence?", vbYesNo)
If Answer <> vbYes Then Exit Sub
ActiveSheet.Unprotect Password:="sharon"
r = ActiveCell.Row
Application.ScreenUpdating = False
Start = Cells(r, "A").Select
ActiveCell.Offset(0, 1).Range("A1:C2").ClearContents
'Selection.ClearContents
'ActiveCell.Offset(0, 3).Range("A1").Select
ActiveCell.Select
ActiveCell.Offset(1, 4).Range("A1:N1").ClearContents
'Selection.ClearContents
Start = Cells(r, "D").Select
ActiveSheet.Protect DrawingObjects:=False, Contents:=True, Scenarios:=
_
True, AllowFormattingCells:=True,
AllowFormattingColumns:=True, Password:="sharon"
Application.ScreenUpdating = True
End Sub

Worksheet code:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'If Target.Column < 5 Then Exit Sub
'If Target.Count > 17 Then Exit Sub
If ActiveCell.Row < 14 Then Exit Sub
'***Begin Column Selector***
Application.ScreenUpdating = False
'If Target.Column = 5 Then
'Target.Columns.ColumnWidth = 20
'Else
'Columns(5).ColumnWidth = 3.33
'End If
If Target.Column = 6 Then
Target.Columns.ColumnWidth = 20
ActiveWindow.Zoom = 85
Else
Columns(6).ColumnWidth = 3.33
ActiveWindow.Zoom = 75
End If
If Target.Column = 7 Then
Target.Columns.ColumnWidth = 20
Else
Columns(7).ColumnWidth = 3.33
End If
If Target.Column = 8 Then
Target.Columns.ColumnWidth = 20
Else
Columns(8).ColumnWidth = 3.33
End If
If Target.Column = 9 Then
Target.Columns.ColumnWidth = 20
Else
Columns(9).ColumnWidth = 3.33
End If
If Target.Column = 10 Then
Target.Columns.ColumnWidth = 20
Else
Columns(10).ColumnWidth = 3.33
End If
If Target.Column = 11 Then
Target.Columns.ColumnWidth = 20
Else
Columns(11).ColumnWidth = 3.33
End If
If Target.Column = 12 Then
Target.Columns.ColumnWidth = 20
Else
Columns(12).ColumnWidth = 3.33
End If
If Target.Column = 13 Then
Target.Columns.ColumnWidth = 20
Else
Columns(13).ColumnWidth = 3.33
End If
If Target.Column = 14 Then
Target.Columns.ColumnWidth = 20
'ActiveWindow.Zoom = 85
Else
Columns(14).ColumnWidth = 3.33
'ActiveWindow.Zoom = 75
End If
If Target.Column = 15 Then
Target.Columns.ColumnWidth = 20
Else
Columns(15).ColumnWidth = 3.33
End If
If Target.Column = 16 Then
Target.Columns.ColumnWidth = 20
Else
Columns(16).ColumnWidth = 3.33
End If
If Target.Column = 17 Then
Target.Columns.ColumnWidth = 20
Else
Columns(17).ColumnWidth = 3.33
End If

Application.ScreenUpdating = True

End Sub

 

[Tonso]

In any event, I would still be interested in learning how to Not
invoke the SelectonChange macro if desired.

 

[Don Guillett]

application.enableevents=false
code
application.enableevents=true

BTW I would have written the selection event differently

 

[Gary''s Student]

1. temporarily dis-able event macro
2. do your thing
3. re-enable event macros:


Sub MyMarco()
Application.EnableEvents = False
'
' do your thing
'
Application.EnableEvents = True
End Sub

 

[Tonso]

application.enableevents=false
code
application.enableevents=true

BTW I would have written the selection event differently

--
Don Guillett
Microsoft MVP Excel
SalesAid Software





- Show quoted text -

Thanks Don...it appears to work perfect!!! I know the code is
crude...but it works and is the best I can do.
Any suggestions you have would of course be appreciated.
Thanks again!

Tonso