Results 1 to 12 of 12

Thread: [RESOLVED] Workbook_Open event won't run

  1. #1

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2002
    Posts
    382

    Resolved [RESOLVED] Workbook_Open event won't run

    Ok, I've used Workbook_Open events many times before without problem. Now I have one that simply will not run and it's driving me crazy. I've recompiled, restarted Excel 2007, and done everything I can think of. I've reduced the code to a simple msgbox command and it's in the workbook module. The Workbook_BeforeClose event works okay. Any body have any ideas why a Workbook_Open event will not run?

    [CODE]
    Private Sub Workbook_Open()
    MsgBox 1
    End Sub
    /CODE]

    Thanks.

  2. #2
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: Workbook_Open event won't run

    Right well it works when i do it, as i suspected it would.

    I suggest that you are probably missing something else, i would probably need to see your workbook to help further though really !!!

    Do your other workbooks with this event still work ? as that would isolate your problem just to this workbook !
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  3. #3
    New Member
    Join Date
    Jan 2009
    Posts
    4

    Re: Workbook_Open event won't run

    Your Events are probably turned off. In the immediate window type: Application.enableevents = true

    hit enter, close down Excel and openit and it should work.

  4. #4
    Frenzied Member zaza's Avatar
    Join Date
    Apr 2001
    Location
    Borneo Rainforest Habits: Scratching
    Posts
    1,486

    Re: Workbook_Open event won't run

    My suspicion is the old "cut and paste a sub" issue; if you copied and pasted the whole thing, including the "Private Sub Workbook_Open()" line, then Excel can sometimes fail to link that with the actual event.

    Delete the whole thing, pick the event from the dropdown so it creates a new event and try it again. I'll be surprised if it doesn't work.
    I use VB 6, VB.Net 2003 and Office 2010



    Code:
    Excel Graphing | Excel Timer | Excel Tips and Tricks | Add controls in Office | Data tables in Excel | Gaussian random number distribution (VB6/VBA,VB.Net) | Coordinates, Vectors and 3D volumes

  5. #5

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2002
    Posts
    382

    Re: Workbook_Open event won't run

    This is wierd. Next day, after rebooting PC, same problem. But, I find if I close the workbook with any sheet active except the first one (on which I eventually want to run an event tha protects it), and then reopen the workbook, the event code runs. If I close with the first one active, it doesn't run. I think there is some kind of corruption in this workbook. But I even deleted every formula, value, format, etc from the sheet and it still fails. This sheet, BTW, is the one where on another post of mine, I get the 1004 error when I run the ExcessFormatCleaner.

  6. #6
    Superbly Moderated NeedSomeAnswers's Avatar
    Join Date
    Jun 2002
    Location
    Manchester uk
    Posts
    2,660

    Re: Workbook_Open event won't run

    can you rip everything out and put it in a new Workbook, and see if it works then ??
    Please Mark your Thread "Resolved", if the query is solved & Rate those who have helped you



  7. #7

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2002
    Posts
    382

    Re: Workbook_Open event won't run

    There are many sheets, dozens of named ranges and cells, and very complex formulas. I've copied the first sheet (the one that is giving me trouble), values only, to a new workbook, and something strange happens. When the original workbook is open, and I close and reopen the new test workbook, the event code in the new test workbook does not execute. If I close the original workbook, then close and reopen the new test workbook, the open event code executes. Not sure what to do next. Rebuilding the entire wb would take days.

  8. #8
    Frenzied Member zaza's Avatar
    Join Date
    Apr 2001
    Location
    Borneo Rainforest Habits: Scratching
    Posts
    1,486

    Re: Workbook_Open event won't run

    And it's simply the msgbox code that won't run? Not with some dependency on another file or some such? That's extremely strange. What about if you run them in separate instances of Excel (ie not in the same application)?
    I use VB 6, VB.Net 2003 and Office 2010



    Code:
    Excel Graphing | Excel Timer | Excel Tips and Tricks | Add controls in Office | Data tables in Excel | Gaussian random number distribution (VB6/VBA,VB.Net) | Coordinates, Vectors and 3D volumes

  9. #9

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2002
    Posts
    382

    Re: Workbook_Open event won't run

    I've already tried that. The new test wb event code works in another Excel instance.

    New info, though. I deleted the first sheet, closed and reopened the wb and the event code runs. So it has something to do with the first sheet.

  10. #10

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2002
    Posts
    382

    Re: Workbook_Open event won't run

    OK, I think I've found the culprit, or at least one of them. When I clear the conditional formatting from the first sheet, everything works (including the XSformatcleaner). When I add it back, it fails. Here's my CF code:

    Code:
    =NOT(IsFormula(B6))
    The rule applies to a named range 'editable_values'. When a user overwrites the formula in a cell within this range with a value, the cells fill color is changed.

  11. #11

    Thread Starter
    Hyperactive Member
    Join Date
    Apr 2002
    Posts
    382

    Re: Workbook_Open event won't run

    Was never able to find out why the CF kept the wb open event from running. I switched the CF formatting which changed the cells interior color to a worksheet change event that changed the cells interior color. Everything working now. Thanks for all your tries to help.

  12. #12
    New Member
    Join Date
    Oct 2020
    Posts
    1

    Re: [RESOLVED] Workbook_Open event won't run

    I know this thread is old, but I wanted to share my solution to this problem for future readers.

    By circumnavigating the problem it wil not be necessary to delete the Conditional Formatting. The strategy is to always open the workbook with a worksheet without CF so that the Workbook_Open Event will be allowed to run. And then after running the Workbook_open event the workbook must automatically make the worksheet with the CF active. This can be achieved in two steps.

    First make sure that the workbook always closes with the worksheet without CF as the active worksheet. In that case by default the next time the workbook will open with that worksheet active. We do that by executing the following code during the Workbook_BeforeClose event on the ThisWorkbook object.

    Example:

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ActiveWorkbook.Sheets("Worksheet_without_CF").Activate
    End Sub

    Second, we set the initial view of the workbook to the worksheet with the CF. This can be achieved by adding the following line of code to the Workbook_Open event on the ThisWorkbook object.

    Example:

    Private Sub Workbook_Open()
    ... your code ...
    ... your code ...
    ActiveWorkbook.Sheets("Worksheet_with_CF").Activate
    End Sub

    You can add your additional code that you want to run during the Worksheet_Open Event. This solution is guaranteed to work because it completely sidesteps the CF problem of the Workbook_Open Event. The workbook will now open on the worksheet with the CF code without us even suspecting that an annoying bug had to be outwitted to achieve our goal!

    If you don't need to have the worksheet with CF opened when opening the workbook then you can leave out the last line of code in the Workbook_Open sub.
    Last edited by curio; Oct 27th, 2020 at 12:58 PM.

Posting Permissions

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



Click Here to Expand Forum to Full Width