Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  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() (XL97 sr2 on Win 2K)

    If I create a blank worksheet and add some trivial code to the Excel Object This Workbook under Workbook Open:

    Private Sub WorkBook_Open()
    MsgBox "hello"
    End Sub

    then save the sheet and close, when I reopen the sheet it behaves as I expect, a box appears carrying the message hello and I click OK to dismiss it.

    BUT when I transfer the exact same code to a more complex sheet (with macros in various modules) I can save, open and close the sheet till I'm blue and as yet I can't get XL to greet me when the sheet opens. As I can't post the problem sheet can anyone offer generic ideas to what would prevent the Workbook_Open() event from happening?

    stuck

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

    Re: Workbook_Open() (XL97 sr2 on Win 2K)

    Are you sure you copied the code to the ThisWorkbook object module?
    If you click inside the Workbook_Open procedure, does the Object dropdown list display Workbook?

  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() (XL97 sr2 on Win 2K)

    It's definitely in the right place, I've just typed it in from scratch.
    In ThisWorkbook, picked Workbook from the left hand dropdown and Open fron the right hand one, XL then created the stub:
    Private Sub Workbook_Open()

    End Sub
    Filled in the gap with Msgbox "hello", saved, closed, reopened via the' official' File Open Dialog (i.e. NOT via the MRU list or a double click from Explorer). Sheet opened , asked about macros, ensured clicked on Enable, got the sheet but no message box to welcome me.

    stuck

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

    Re: Workbook_Open() (XL97 sr2 on Win 2K)

    I'm sorry, I can't explain this. I found two suggestions in the newsgroups, but frankly, they seem improbable:

    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. I can't reproduce this on my system, however.

  5. #5
    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() (XL97 sr2 on Win 2K)

    <img src=/S/thewave.gif border=0 alt=thewave width=225 height=33> and the crowd go wild as, yet again, Hans nails it!!!!

    It may be improbable but option 2) is the cause. My sheet is actually a template and until now it hasn't mattered that some cells return #DIV0 on opening, they go away when data is entered. However, some of these errors are from UDFs. Having temporarily removed all my UDFs, the Workbook_Open event works fine and I can make it say hello.

    Now all I've got to do is go through the sheet and trap those minor errors so that the UDFs don't try and kick in before they are actually needed and then I can go on to try and get the Workbook_Open event to do something a little more powerful.

    I'll be back next time I'm...

    stuck

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

    Re: Workbook_Open() (XL97 sr2 on Win 2K)

    Thanks for this, Hans.

    I've just further complicated one of my templates with data validation and more formulas (and I need to do yet more), and Workbook_Open won't fire any more (Before_Print fires fine). If I take the same template, strip all the sheets except one and take all the formulas and data validation out of that, save it and reopen, the Open event fires again. I have ended up having to save the template with Calculation set to Manual, and set the last two lines in the Workbook_Open code to:

    Application.Calculation = xlAutomatic
    Application.CalculateFullRebuild ' I know, belt and suspenders

    and then the Event fires when the gutted template is opened. I'm going to mess with different sections of this template to see if I can narrow it down to a more limited group of culprits, but meanwhile this ugly solution works.

    However, I'm open to other suggestions.
    -John ... I float in liquid gardens
    UTC -7ąDS

  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: Workbook_Open() (XL97 sr2 on Win 2K)

    Followup. I had a number of cells in one sheet (intended to be filtered and printed out) that were dependent on one cell which contained =TODAY(). When I deleted the =TODAY() formula, the Workbook_Open event fires.
    -John ... I float in liquid gardens
    UTC -7ąDS

  8. #8
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open() (XL97 sr2 on Win 2K)

    Do you have any code in the event routines for the sheet that contains the =TODAY(), in particular in the Worksheet_Calculate event routine? If so, does this code contain a statement like:

    <pre> Application.EnableEvents = False
    </pre>

    Legare Coleman

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

    Re: Workbook_Open() (XL97 sr2 on Win 2K)

    Good question, but no, there are no Sheet Object event routines on any sheet, there are only the Workbook Object events Open and Before_Print. There are no UDF's. At this point I'm maintaining the template with Calculation = xlAutomatic, and chaining all =TODAY() references to one cell, leaving it blank, and then pasting the formula in via the second last line of the Open event code, and the Open event is now firing properly. I was formerly using Application.EnableEvents = False as the opening of the Workbook_Open code, and setting Application.EnableEvents = True before the end of that sub, but I have removed both Application.EnableEvents = Boolean lines from the routine.
    -John ... I float in liquid gardens
    UTC -7ąDS

  10. #10
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open() (XL97 sr2 on Win 2K)

    (I'm using XL2k/sr3/W2k)

    Here is the complete extent of code in a workbook that I have:

    (In "ThisWorkbook")
    -------------------------------------------
    Option Explicit

    Private Sub WorkBook_Open()
    MsgBox "hello"
    End Sub

    Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
    Application.EnableEvents = False
    Sh.Range("A2").Value = FormatDateTime(Now, vbLongDate) & " at " & FormatDateTime(Now, vbLongTime)
    Application.EnableEvents = True
    End Sub
    -------------------------------------------

    The WorksheetChange() event worked fine... I added the the Workbook_Open() routine, but it did not work. From your post, I have commented out the Workbook_Open() routine, and now not even my Worksheet_Change() event works!

    Ideas?

    thanks,
    ..dane


    (update) I figured out that macros were disabled; When I set security to medium and select "Enable Macros" when I open the document, both the workbook_open() and workbook_sheetchange() functions operate normally. Funny thing is, I never turned the macros off, and the workbook_sheetchange() function worked fine until I added the workbook_open() event. But then neither worked anymore... Can some VB-code execute without Macros, but other VB-code requires macros to be enabled?

    thanks,
    ..dane

  11. #11
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open() (XL97 sr2 on Win 2K)

    Put this line of code in the VB Immediate window and execute it:

    <pre> Application.EnableEvents = True
    </pre>


    Make this line of code a comment:

    <pre> Application.EnableEvents = False
    </pre>


    Does the workbook open event work as expected now?
    Legare Coleman

  12. #12
    2 Star Lounger
    Join Date
    Oct 2002
    Posts
    114
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open() (XL97 sr2 on Win 2K)

    Legare,

    When I do that, I get the following message:

    ---------------------------
    Microsoft Visual Basic
    ---------------------------
    The macros in this project are disabled. Please refer to the online help or documentation of the host application to determine how to enable macros.
    ---------------------------
    OK Help
    ---------------------------


    However, if when I open the document I enable macros, everything works fine.

    What's strange is that until I added the workbook_open() event routine, I was never even prompted to enable or disable macros... now I am...

    thanks,
    ..dane

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

    Re: Workbook_Open() (XL97 sr2 on Win 2K)

    The Workbook_Open event is Visual Basic code, this counts as a macro. Also see <post#=381518>post 381518</post#>.

  14. #14
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Workbook_Open() (XL97 sr2 on Win 2K)

    Before you added the code, you did not have any macros in it so excel did not see a need to ask if you wanted them disabled since it was a moot point. When you added a macro you got asked.

    If you disable macros, your events will not trigger macros since the macros are disabled.

    Steve

  15. #15
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Workbook_Open() (XL97 sr2 on Win 2K)

    First, do you have macro security set to High? If so, that could be causing this problem. If you are going to use macros, you should probably have it set to medium.

    Second, I thought that you said that you already have a Worksheet_Change event routine in the workbook. If so, you should have been asked if you want macros enabled when you opened the workbook. The only thing I can think of is that the only macro(s) in the workbook were event routines and somehow EnableEvents was set to False for the workbook, and since the macros would not execute Excel was not asking if macros should be enabled.
    Legare Coleman

Page 1 of 2 12 LastLast

Posting Permissions

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