Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Endless sheet activate loop (Excel 2000/sr1)

    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

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Endless sheet activate loop (Excel 2000/sr1)

    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

  3. #3
    3 Star Lounger
    Join Date
    Nov 2002
    Posts
    387
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Endless sheet activate loop (Excel 2000/sr1)

    Thanks that fixed it!!

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Endless sheet activate loop (Excel 2000/sr1)

    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.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •