Results 1 to 4 of 4
  1. #1
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    workbook_open & workbook calculation (XL97 and XL

    I have a workbook that makes use of the workbook_open event. Amongst other things the code presents an imput box and the string the user enters is written to a protected cell. It behaves as I expect in both XL97 and 2K if it is the sole workbook in that instance of Excel.

    However, if one of my other workbooks (that includes some UDFs) is already loaded then:
    In XL2K, no problems all is well
    In XL97, the writing of the text string from the input box to a cell is a change and that seems trigger a recalc of all open workbooks. Fair enough, but while XL97 is off redoing all the calcs and UDFs in the other workbook it has as senior moment and never returns to finish off the rest of the workbook_open code.

    Is there a way to keep XL97's mind on the job and not go off and recalc any other open workbooks until it has finished dealing with the code in hand? Will it be as trivial as setting the calc to manual as the first line of the workbook _open event? If so how? I've tried:
    Application.Calculation = xlCalculateManual
    but that gives me an "Application-defined or object-defined error"

    stuck

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: workbook_open & workbook calculation (XL97 and XL

    Try setting Application.EnableEvents to False at the beginning of the Workbook_Open procedure. This will prevent other events from occurring while the present code is executed. Don't forget to set Application.EnableEvents to True at the end:

    Private Sub Workbook_Open()
    Application.EnableEvents = False
    ' your code goes here
    Application.EnableEvents = True
    End Sub

  3. #3
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: workbook_open & workbook calculation (XL97 and

    Thank you Hans for yet another high speed response. It seems I need to both to disable events AND disable calculation, i.e. this construct:

    Application.EnableEvents = False
    Application.Calculation = xlCalculationManual
    ' code goes here
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15> (un)stuck

  4. #4
    5 Star Lounger
    Join Date
    Apr 2001
    Location
    Arriving Somewhere but not Here
    Posts
    698
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: workbook_open & workbook calculation (XL97 and

    It seems I was too hasty, and my previous post doesn't solve anything. XL97 still gets distracted by my UDFs. Until I have time to revisit the code in the UDFs and ses if things can be improved there, I'm going to ask the user to close all other workbooks before they continue.

    Unless anyone has anything else to offer on this issue?

    stuck

Posting Permissions

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