+ Reply to Thread
Results 1 to 3 of 3

Workbook_SheetCalculate looping

  1. #1
    Forum Contributor
    Join Date
    03-21-2004
    Location
    Norwich, CT USA
    MS-Off Ver
    Excel 2010
    Posts
    163

    Workbook_SheetCalculate looping

    I have a macro that I need to run when ever a calculation is performed on the activesheet.

    I am using the following code, in the workbook object to carry that out:
    Private Sub Workbook_SheetCalculate(ByVal Sh As Object)

    With ActiveSheet

    SheetNumberUpdate

    End With

    End Sub
    It does fire when the calculation occurs and runs the macro, however once it continues to run cycling back through the macro. Lather, rinse, repeat!

    I tried placing the above code in the module with the macro but then it doesnt work at all.

    Help!!!!!!!!!!

  2. #2
    Jim Thomlinson
    Guest

    RE: Workbook_SheetCalculate looping

    You have a recursive loop. You should be able to fix it with

    application.enableevents

    AS with all application level settiing it should have an error handler to
    deal with the potential of a crash

    sub Whatever()
    on error goto ErrorHandler
    application.enableevents = false
    'do your stuff

    Errorhandler:
    application.enableevents = true
    end sub
    --
    HTH...

    Jim Thomlinson


    "Hammer_757" wrote:

    >
    > I have a macro that I need to run when ever a calculation is performed
    > on the activesheet.
    >
    > I am using the following code, in the workbook object to carry that
    > out:
    >
    > Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    >
    > With ActiveSheet
    >
    > SheetNumberUpdate
    >
    > End With
    >
    > End Sub
    >
    > It does fire when the calculation occurs and runs the macro, however
    > once it continues to run cycling back through the macro. Lather,
    > rinse, repeat!
    >
    > I tried placing the above code in the module with the macro but then it
    > doesnt work at all.
    >
    > Help!!!!!!!!!!
    >
    >
    > --
    > Hammer_757
    > ------------------------------------------------------------------------
    > Hammer_757's Profile: http://www.excelforum.com/member.php...fo&userid=7413
    > View this thread: http://www.excelforum.com/showthread...hreadid=400466
    >
    >


  3. #3
    Forum Contributor
    Join Date
    03-21-2004
    Location
    Norwich, CT USA
    MS-Off Ver
    Excel 2010
    Posts
    163

    Thumbs up

    Thanks Jim, that works.

    the education continues

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1