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

    Worksheet activate event being called over and ove (Excel 2000/2)

    Below is recorded macro code that copys the contents of a named range from one sheet named "Input" to another cell range on another sheet named "Summary" and it works fine from a button located on the Summary sheet. What we are trying to do now is call the same macro whenever the Summary sheet is activated but when we do the macro gets called repeatedly which I am sure is due to the Summary sheet activate event being fired over and over again. How can we correct this?

    Sub SumExp()
    '
    ' SumExp Macro
    ' Macro recorded 1/1/2003 by Kilpatrick
    '

    '
    Sheets("Summary").Select
    Range("A13:G230").Select
    Selection.EntireRow.Delete
    Sheets("Input").Select
    Range("DAInputA").Select
    Selection.Copy
    Sheets("Summary").Select
    Range("B13").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("B14"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2
    Range("F11").Select
    End Sub

  2. #2
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Worksheet activate event being called over and ove (Excel 2000/2)

    Assuming it's being called by a Worksheet_Change Event*, the change event is being recursively called, so in the Worksheet_Change Event you need to insert:

    Application EnableEvents = False

    before the call to the above code and

    Application EnableEvents = False

    after the call. Legare explains it <!post=here,209941>here<!/post>.

    [Edit]
    * ... or the Worksheet_Activate Event, or the Worksheet_SelectionChange Event
    -John ... I float in liquid gardens
    UTC -7ąDS

  3. #3
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Worksheet activate event being called over and ove (Excel 2000/2)

    FWIW, without testing (IOW, you need to test this first!), your macro can read:

    Sheets("Summary").Range("A:G").EntireRow.Delete
    Sheets("Input").Range("DAInputA").Copy Sheets("Summary").Range("B13")
    Application.CutCopyMode = False
    With Worksheets("Summary")
    .Select ' only because you want this sheet to be the active one when done
    .Range("you'll need to enter the correct range here").Sort Key1 ...
    .Range("F11").Select
    End With

    One of the annoyances of the macro recorder is that it encodes all your selection activity, but 99% of the time the actions can be performed without selecting.
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Worksheet activate event being called over and ove (Excel 2000/2)

    Thanks but I am calling this macro from the worksheet "Activate" event. I have tried what you said but it doesn't work but I did find a solution as shown below. I am sure it is not the really best way. Note the comments explaining what I did.

    ' Whether the button is clicked or moving to the summary page, make sure the Summary page is the one selected
    Sheets("Summary").Select

    Range("A13:G230").Select
    Selection.EntireRow.Delete

    'Sheets("Input").Select ' Must not do this because this makes the Input page active which was the cause of the flashing problem

    ' Use the Range Copy method to copy the contents of the named ranges "DAInputA" from the Input workshhet
    Range("Input!DAInputA").Copy

    'Selection.Copy ' Not needed
    'Sheets("Summary").Select ' Not needed since remaining on Summary page

    Range("B13").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.Sort Key1:=Range("B14"), Order1:=xlAscending, Header:=xlGuess, _
    OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom
    Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array(4), _
    Replace:=True, PageBreaks:=False, SummaryBelowData:=True
    ActiveSheet.Outline.ShowLevels RowLevels:=2

    Range("F11").Select

  5. #5
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Worksheet activate event being called over and ove (Excel 2000/2)

    The flashing problem may be as simple as using:

    Application.ScreenUpdating = False

    at the beginningof the code and

    Application.ScreenUpdating = True

    at the end. See also my other reply.
    -John ... I float in liquid gardens
    UTC -7ąDS

  6. #6
    Uranium Lounger
    Join Date
    Dec 2000
    Location
    Salt Lake City, Utah, USA
    Posts
    9,508
    Thanks
    0
    Thanked 6 Times in 6 Posts

    Re: Worksheet activate event being called over and ove (Excel 2000/2)

    And in which case you could start out like this:

    Sheets("Summary").Select
    Range("A:G").EntireRow.Delete
    Worksheets("Input").Range("DAInputA").Copy Range("B13")
    -John ... I float in liquid gardens
    UTC -7ąDS

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

    Re: Worksheet activate event being called over and ove (Excel 2000/2)

    Thanks again!! I figured it would most likely be a simple solution and you code segment of shorter code is great. I am new to Excel coding if you can't already tell.

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

    Re: Worksheet activate event being called over and ove (Excel 2000/2)

    Just a technicality John, but I don't like setting EnableEvents to False. It is an application-wide setting and thus may prevent applications outside your own from working properly. Also when debugging, the EnableEvents=True statement is often not reached.

    I prefer dimensioning a public variable :
    Public bDisableEvents As Boolean

    Then in event subs I use:

    Private sub Whatever()
    If bDisableEvents Then Exit Sub
    bDisableEvents=True
    '......
    '......
    '......
    bDisableEvents=False
    End Sub

    So when my project gets reset (e.g. due to ending after an error), Events are still enabled for other 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
  •