Results 1 to 13 of 13
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    378
    Thanks
    1
    Thanked 0 Times in 0 Posts

    AutoOpen doesn't always run (Excel97/NT)

    Hi

    I'm just curious. My spreadsheet contains the following macro
    <pre> Private Sub Workbook_Open()
    Call Tools.ACC3_Load
    End Sub
    </pre>


    This automatically runs Tools.ACC3_Load, which displays a dialog box. It works great, but I've discovered that it doesn't activate if there's a particular spreadsheet already open. I've experimented, and found that the problem seems to be caused by one particular tab. If I delete all but that tab, the problem persists. If I delete the problem tab, the problem disappears.

    The spreadsheet that causes the problem (attached) doesn't contain any macros.

    Curious, eh. It not a great problem, but I'd be interested if anyone has any ideas.

    Thanks
    Dale

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

    Re: AutoOpen doesn't always run (Excel97/NT)

    Why does the attached workbook have formulas referring to C:Program FilesMicrosoft OfficeOffice10BIBLIOANALYSISATPVBAEN.XLA ? It makes the formulas fail on my machine. It's better to tick Analysis Toolpak in Tools | Add-Ins..., you can then use the NETWORKDAYS function without external references.

    Other than that, no idea.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    378
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: AutoOpen doesn't always run (Excel97/NT)

    Hi Hans

    I didn't realise, and I have no idea why. (It was originally set up as you suggest, but I've changed PC's lately, so maybe it's got screwed up in the process.)

    Tomorrow I'll try regularised that aspect. You never know, it might be causing my problem.

    Thanks for your help
    Dale

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    378
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: AutoOpen doesn't always run (Excel97/NT)

    Hi Hans

    I fixed the references to C:Program FilesMicrosoft Office etc, and it didn't help.

    To recap:
    <UL><LI>Spreadsheet #1 has a Workbook_Open procedure, which should activate whenever the spreadsheet is opened. Spreadsheet #2 contains a formula using the Networkdays function.
    <LI>Open #1 when #2 is already open, and #1's Workbook_Open procedure doesn't activate. Open #1 when #2 is NOT already open, and #1's Workbook_Open procedure DOES activate[/list]I tried creating a new #2 from scratch (just one cell, containing the Networkdays function), and the problem persists. I tried a different #1, which also contains a Workbook_Open procedure, and it did NOT have the problem.

    So, looks like the problem is caused by something in #1. Unfortunately, it's a badly-written monster I inherited, which makes any sort of logical analysis difficult, to say the least.

    Ah well, I'll live with it. Weird, though.
    Dale

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

    Re: AutoOpen doesn't always run (Excel97/NT)

    The newsgroups mention two possible causes for an automatic macro failing to execute:

    1. A timing problem: too much calculating is going on while the workbook is being opened.

    2. The workbook contains formulas with user-defined functions; if an error occurs while recalculating a function, code execution would halt before Workbook_Open has been executed.

    Apparently the specific combination of spreadsheets #1 and #2 triggers one of these, but from your remark I gather it may not be easy to find the exact cause.

  6. #6
    Platinum Lounger
    Join Date
    Jan 2001
    Posts
    3,788
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: AutoOpen doesn't always run (Excel97/NT)

    Try running the core of the Workbook_Open macro after the file has finished opening. This can be done using the following method

    Replace the Workbook_Open macro with this:

    <code>Private Sub Workbook_Open()</code>
    <code> Application.OnTime Now, "Complete_Open"</code>
    <code>End Sub</code>

    In a normal module add the following:

    <code>Sub Complete_Open()</code>
    <code> Call Tools.ACC3_Load</code>
    <code>End Sub</code>

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

    Re: AutoOpen doesn't always run (Excel97/NT)

    <P ID="edit" class=small>(Edited by JohnBF on 23-Sep-04 14:50. )</P>Dale, Hans reminded me of another possibility. A 400 kb template of mine started experiencing autoopen failures. When I replaced the =TODAY formulas with a datestamp, the problem went away. Try saving your worksheet without the formulas and stamping them as one of the last items in your autoopen routine.

    Edit: also turn calculation to manual and enableevents to false until the last lines of the autoopen code, at which point reenable automatic calculation and events.

    What's weird to me is that =NOW() doesn't cause the same problem; I expect the two functions are coded differently within Excel.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    378
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: AutoOpen doesn't always run (Excel97/NT)

    Thank you all.

    Hans: I suspect this isn't "too much calculating to do" or "error in user-defined function". I've put a Stop command as the first statement in Workbook_Open, and it never executes. So it looks like Workbook_Open is not even starting. (Although, on opening #1, the hour glass does appear momentarily.)

    Tony: The "Application.OnTime" didn't help.

    John: Replaced all TODAY with NOW, and temporarily disabled AutoCalculation and Events, to no avail. (I didn't understand what "stamping them as one of the last items in your autoopen routine" meant. Have I done sufficient to test your theory?)

    I'm willing to give up. But if there are any more ideas ...

    Thanks
    Dale

  9. #9
    3 Star Lounger Jim Cone's Avatar
    Join Date
    Feb 2002
    Location
    Portland, Oregon, USA
    Posts
    238
    Thanks
    0
    Thanked 3 Times in 3 Posts

    Re: AutoOpen doesn't always run (Excel97/NT)

    Dale,

    One more thing for you to try...
    Instead of placing your code the ThisWorkbook Open Event,
    in a regular module use an Auto_open sub....

    Sub Auto_open

    your code

    End Sub

    I have seen posts saying that auto_open is more reliable, but I have never had to really test it,
    as I have never had a failure with with either method.
    But it won't hurt to try. I have had an a couple of Auto_open subs working without a hitch for several years.
    I have also found that the order of your code statements can affect things, so some experimenting there could help.

    Regards,
    Jim Cone
    San Francisco, CA

  10. #10
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    378
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: AutoOpen doesn't always run (Excel97/NT)

    Bingo! Works like a bought one. Thanks, Jim, and thanks everyone else for their suggestions.

    Dale

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

    Re: AutoOpen doesn't always run (Excel97/NT)

    Since Jim has solved your problem it doesn't matter, but instead of leaving formulas with =TODAY on the sheet, I just run code such as

    Worksheets("mysheet").Range("E1").Value = Date

    which stamps the date every time the sheet is opened. In your case you would figure the calculations for the three cells in VBA:

    With Worksheets("Manuals")
    .Range("D28").Formula = VBA equivalent of "=NETWORKDAYS((TODAY()-1),DATE(2004,3,1))/NETWORKDAYS(DATE(2004,3,26),DATE(2004,3,1))"
    .Range("F28").Formula = VBA equivalent of "=NETWORKDAYS((TODAY()-1),DATE(2004,3,27))/NETWORKDAYS(DATE(2004,3,31),DATE(2004,3,27))"
    .Range("H28").Formula = <etc.>
    End With

    As a test, I'm curious to see how the Workbook Open event macro behaves when the workbook is opened with cells D28, F28 & H28 empty.
    -John ... I float in liquid gardens
    UTC -7ąDS

  12. #12
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wellington, Wellington, New Zealand
    Posts
    378
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: AutoOpen doesn't always run (Excel97/NT)

    Hi John

    If you delete those cells, the problem doesn't occur. But, conversely, the problem also doesn't occur if you don't delete the cells, but use a newly-created #1 spreadsheet with a Workbook_Open in it.

    It seems that NETWORKDAYS is not the cause of the problem (something in #1 is), but it may be a necessary part of the problem.

    It's 4:00pm on Friday, here, and I seem to be conducting a philosophical debate with myself. I think I'll go home soon.

    Dale

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

    Re: AutoOpen doesn't always run (Excel97/NT)

    Dale, it's not the NETWORKDAYS() function that I removed from my template, it was the TODAY() function. Since you have gone home, now I'm arguing with myself. And Friday is still to come for me. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15> <img src=/S/laugh.gif border=0 alt=laugh width=15 height=15>
    -John ... I float in liquid gardens
    UTC -7ąDS

Posting Permissions

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