Results 1 to 12 of 12
  1. #1
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Workbook objects loading after Open event (2003)

    Sorry for the strange title, I'll try and explain what I mean. I have a workbook that contains some code in the Workbook_Open event. That code refers to some of the worksheets within the workbook by name (I've renamed them to shtData, shtModel, etc.). The code crashes at that point, stating that shtData (for example) is not defined. At this stage, the Project Explorer window is completely empty, so it's as if VBA hasn't loaded the workbook objects before running the Open event. If I click "End", the objects then proceed to load. Running the Workbook_Open code after they have loaded works perfectly.

    Perhaps more interestingly, it only happens with the combination of my computer and this particular workbook. All other Excel files are behaving normally on my machine, and the workbook works without any glitches on other machines in the office (all the same spec). I've tried running Detect & Repair on my machine but it didn't solve the problem.

    I've had a few similar problems with this workbook before during its development, and have had to copy everything bit by bit to a new blank workbook to overcome the corruption. I'll proceed to do the same again now (and expect it will work properly then) but thought I'd mention it here to see if anyone has heard of anything similar. As I say, the most confusing thing is that it only seems to occur on my machine. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  2. #2
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook objects loading after Open event (200

    A bit of an update - how I open the workbook seems to make a difference. If I open Excel first and then open the workbook, it works properly; if I open it (with Excel not running) by double-clicking on it in Windows Explorer, it falls over as described.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

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

    Re: Workbook objects loading after Open event (2003)

    Since the problem only occurs in a particular workbook on a particular PC, the obvious suggestion is "don't open that workbook on that PC". <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    But seriously, it's difficult to imagine what could cause this. You could try Jan Karel Pieterse's <!post=Systematic Approach to Behavioral Problems in XL,290455>Systematic Approach to Behavioral Problems in XL<!/post>.

    If you'd like to check whether it's a timing conflict, you could create a copy of the workbook, and in this copy move all the code from the Worksheet_Open event procedure to a sepearate procedure, say MyOpen, in a standard module. Then call it like this:

    Private Sub Workbook_Open()
    Application.OnTime Now + TimeSerial(0, 0, 2), "MyOpen"
    End Sub

    This will build in a delay of 2 seconds.

  4. #4
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook objects loading after Open event (200

    Good thinking Hans, setting a delay is a genius idea. Works perfectly now!
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

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

    Re: Workbook objects loading after Open event (200

    Actually, the 2 seconds delay is not needed. You set the OnTime method to fire a macro. Excel then proceeds to finish initialising itself, opening your file properly etcetera. Then when that is done, the ontime macros are fired.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  6. #6
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook objects loading after Open event (200

    Ok, thanks. Before now I assumed that all the initialisation occurred before any events were run, including the Workbook_Open event. Was that assumption wrong?
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  7. #7
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook objects loading after Open event (200

    Although the Workbook_Open procedure now runs smoothly, there's another strange twist (one I can live with this time): the mousewheel add-in doesn't work unless I already had Excel running before opening the workbook.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  8. #8
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook objects loading after Open event (200

    Thanks JK, some useful info and very handy advice there. My Workbook_Open events will never be the same again!
    <img src=/S/thankyou.gif border=0 alt=thankyou width=40 height=15>
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  9. #9
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook objects loading after Open event (200

    I do seem to have a problem with Excel on my computer; (there may also be a bug in that particular workbook, but I'll come back to that later).

    If I double-click on any workbook in Windows Explorer (or on my desktop, etc.), my personal macro workbook does not open and the Mousewheel fix add-in (<post:=389,439>post 389,439</post:>) does not load. I've also added a file in C:Program FilesMicrosoft OfficeOFFICE11XLSTART with the following code:

    <div style="width: 100%; background-color: #FFFFFF;">Private Sub Workbook_Open()
    MsgBox "Hello"
    End Sub</div hiblock>

    The code doesn't run when I open workbooks from Windows Explorer. But when I open Excel any other way, everything opens up correctly. I've gone through Startup Problems as suggested by HansV, but unfortunately there's no change. I'm considering a complete uninstall and reinstall of Office, but before I get that drastic are there any other suggestions?

    Cheers,
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

  10. #10
    5 Star Lounger
    Join Date
    Apr 2003
    Location
    Hampshire, United Kingdom
    Posts
    602
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Solved (I think)

    Ok, feeling slightly silly now. I checked in Task Manager to see if there was a copy of Excel already running in the background; sure enough there was. I ended that process and hey presto, everything works perfectly. I normally "hibernate" my laptop rather than shutting down completely, so that's why it was still not working today after discovering the problem yesterday.
    Waggers
    If at first you do succeed, you&#39;ve probably missed something.

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

    Re: Solved (I think)

    Especially Excel 2000 tends to stay behind as a hidden process after a crash. But other versions do that from time to time too. So: if you ever had a crash of Excel, make sure you check task manager (process tab) to see if any Excel.exe is still there. If you have no Excel window open and there is still one there, kill it.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

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

    Force Open code to run after initialization (XL)

    You were wrong (as you proved yourself <smile>). It is indeed possible for Excel not being completely initialised when the workbook_Open event runs.

    I almost always create a normal sub in a normal module called "ContinueOpen", which I start with an ontime event in Workbook_Open in the ThisWorkbook module:

    <pre>Option Explicit

    Private Sub Workbook_Open()
    Application.OnTime Now, "ContinueOpen"
    End Sub
    </pre>


    And in a normal module:

    <pre>Option Explicit

    Sub ContinueOpen()
    'Code to build toolbars, initialise application, etcetera goes here.
    End Sub
    </pre>


    This has another advantage. Code in Thisworkbook (TWB) cannot be "Cleaned". If you heavily edit the code in TWB, you build up all sorts of garbage Excel (the VBE) does not tidy up properly.
    You may end up with a corrupt TWB module in the end. A normal module however, can be exported, removed and imported again, thus getting rid of the debris. If your TWB needs to be cleaned, all you can do is recreate the workbook. A lot of work, even if you only need to copy all worksheets.
    Rob Bovey's Code Cleaner is highly recommended for the frequent programmer.
    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
  •