Endless sheet activate loop (Excel 2000/sr1)

[Jim Bassett]

I have a macro that clears a sheet named summary then copies some cells from another sheet. I waht this macro to be called anytime the summary sheet is selected so I call the macro in the sheetactivate event. But what is happening is it gets caught in an endless loop, the activate event is being generated for the sheet named summary over and over again. Here is the Macro which is being called from the summary sheet's "Worksheet_Activate" event

Sub CopyIP()
'
' CopyIP Macro
' Macro recorded 4/3/2003 by PKilpatrick
Dim Text As Range
Dim Dar As Range
Dim destination1 As Range
Dim loc1 As Range

'Delete old area of Summary
Sheets("summary").Select
Rows("26:75").Select
Selection.EntireRow.Delete


Application.ScreenUpdating = False



'Copy Range Text
Sheets("Input").Select
Application.CutCopyMode = False

Range("Text").Copy
Sheets("Summary").Select
Range("B26").Select
ActiveSheet.Paste

Range("C22").Activate
Selection.Copy

Sheets("Summary").Select
ActiveWindow.SmallScroll Down:=12
Range("B27").Select
ActiveSheet.Paste
Selection.Paste

Sheets("Input").Select
Range("DAR").Select
Application.CutCopyMode = False
Selection.Copy

Sheets("Summary").Select

Range("F27").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Application.CutCopyMode = False

'formats data copied
Selection.NumberFormat = "_(* #,##0_);_(* (#,##0);_(* ""-""_);_(@_)"
Selection.Interior.ColorIndex = 36

Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone


With Selection.Borders(xlEdgeBottom)
.Weight = xlThick
.ColorIndex = xlAutomatic
End With


Range("B2650").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
Selection.Borders(xlEdgeTop).LineStyle = xlNone
Selection.Borders(xlEdgeBottom).LineStyle = xlNone
Selection.Borders(xlEdgeRight).LineStyle = xlNone
Selection.Borders(xlInsideVertical).LineStyle = xlNone
Selection.Borders(xlInsideHorizontal).LineStyle = xlNone
Selection.Interior.ColorIndex = 2
Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
ActiveWindow.SmallScroll up:=40
Range("f28").Select
Selection.End(xlDown).Select
Selection.NumberFormat = "_($* #,##0_);_($* (#,##0);_($* ""-""_);_(@_)"
Application.ScreenUpdating = False
Range("f16").Select


End Sub

[sdckapr]

Try adding:
<pre>Application.EnableEvents = False</pre>

at the beginning (to stop triggering and retriggering the "activation"

At the end add:
<pre>Application.EnableEvents = True</pre>


to reenable it.

Steve

[Jim Bassett]

Thanks that fixed it!!

[pieterse]

Hi Steve,

I don't like using EnableEvents=False.

I prefer something like this:

'Top of event module
Dim bDisableEvents as Boolean

Private Sub WhateverEvent()
If bDisableEvents Then Exit Sub
bDisableEvents =True
'The code
bDisableEvents = False
End Sub

This way, when your code is END-ed due to an (unexpected) error, EnableEvents is not affected.
Also, this way one does not interfere with events used by other (XL) projects.