Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable running of SelectionChange macro when in another macro?
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable running of SelectionChange macro when in another macro?
As ALWAYS post your code for comments. There is rarely a need to select
-- Don Guillett Microsoft MVP Excel SalesAid Software "Tonso" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable running of SelectionChange macro when in another macro?
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable running of SelectionChange macro when in another macro?
In any event, I would still be interested in learning how to Not
invoke the SelectonChange macro if desired. |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable running of SelectionChange macro when in another macro?
application.enableevents=false
code application.enableevents=true BTW I would have written the selection event differently -- Don Guillett Microsoft MVP Excel SalesAid Software "Tonso" wrote in message ... In any event, I would still be interested in learning how to Not invoke the SelectonChange macro if desired. |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable running of SelectionChange macro when in another macro
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 -- Gary''s Student - gsnu201001 "Tonso" wrote: In any event, I would still be interested in learning how to Not invoke the SelectonChange macro if desired. . |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Disable running of SelectionChange macro when in another macro?
On Mar 21, 2:11*pm, "Don Guillett" wrote:
application.enableevents=false code application.enableevents=true BTW I would have written the selection event differently -- Don Guillett Microsoft MVP Excel SalesAid Software "Tonso" wrote in message ... In any event, I would still be interested in learning how to Not invoke the SelectonChange macro if desired.- Hide quoted text - - 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Worksheet Functions | |||
I tried to get around the problem of the pivot table field settingdefaulting to Count instead of Sum by running a macro of change the settingfrom Count to Sum. However, when I tried to run the Macro, I got error messageof run time error 1004, unable | Excel Discussion (Misc queries) | |||
Event Macro running another macro inside | Excel Discussion (Misc queries) | |||
disable user running macro from Tools Macro | Excel Discussion (Misc queries) | |||
Disable SelectionChange Event | Excel Discussion (Misc queries) |