Disable running of SelectionChange macro when in another macro?
in [General]
|
From: Tonso on 21 Mar 2010 13:12 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
From: Don Guillett on 21 Mar 2010 13:24 As ALWAYS post your code for comments. There is rarely a need to select -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Tonso" <wthomasss(a)hotmail.com> wrote in message news:84856a89-1aa5-4ccc-959f-6ecc705370e9(a)j21g2000yqh.googlegroups.com... > 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
From: Tonso on 21 Mar 2010 13:34 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
From: Tonso on 21 Mar 2010 13:35 In any event, I would still be interested in learning how to Not invoke the SelectonChange macro if desired.
From: Don Guillett on 21 Mar 2010 14:11
application.enableevents=false code application.enableevents=true BTW I would have written the selection event differently -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett(a)gmail.com "Tonso" <wthomasss(a)hotmail.com> wrote in message news:6f01db2f-c405-44ab-a012-e2d6851b123a(a)19g2000yqu.googlegroups.com... > In any event, I would still be interested in learning how to Not > invoke the SelectonChange macro if desired. |