none
Workbook_Open handler not invoked when Workbook is opened RRS feed

  • Question

  • I have an xlsm workbook which currently uses Auto_Open to perform some initialization processing which includes display of a UserForm and associated handlers for the buttons on the UserForm. I'm trying to convert from using Auto_Open (which I'm told is "obsolete") to using a Workbook_Open event handler within the Thisworkbook module within the list of Microsoft Excel Objects. I cut/pasted the logic from the Auto_Open routine within my code module into the Private Sub Workbook_Open() within the Excel Objects module, then deleted the shell of the Auto_Open routine.  But now when I open the workbook there is no evidence that any code within Workbook_Open is ever executed.  Is there some setting I'm missing somewhere to enable event handling?

    cw

    Thursday, August 27, 2015 2:52 PM

Answers


  • The function is intended to allow a conditional format several cells in a sheet to use a formula similar to "=NOT(ThisFolderExists($B$7))" to change the cell background fill when the folder specified in $B$7 doesn't exist.


    Wow! That is a crazy idea. :-)

    I have played around a bit and found some interesting results:

    a) The Workbook_Open event does not fire using this function in a conditional formatting:

    Function ThisFolderExists(ByVal FolderSpec As String) As Boolean
    End Function

    b) The Workbook_Open event does fire using this function in a conditional formatting:

    Function ThisFolderExists(ByVal FolderSpec) As Boolean
    End Function

    c) The Workbook_Open event does not fire using this function in a conditional formatting:

    Function ThisFolderExists(ByVal FolderSpec) As Boolean
      ThisFolderExists = Dir(FolderSpec) <> ""
    End Function

    d) The Workbook_Open event does fire using the c) function in a cell and using a conditional formatting with that cell as reference.

    The issue exists in XL2007 and all versions above, but not in XL2003 or below.

    Furthermore any disc operation that is called from a conditional formatting seems to disturb the event handling in Excel. I recommend to remove the formula from the conditional formatting.

    Andreas.
    • Marked as answer by CharlieWright Sunday, August 30, 2015 2:47 PM
    • Unmarked as answer by CharlieWright Sunday, August 30, 2015 2:47 PM
    • Marked as answer by CharlieWright Sunday, August 30, 2015 6:31 PM
    Sunday, August 30, 2015 5:45 AM

All replies

  • Is there some setting I'm missing somewhere to enable event handling?


    No, but maybe the code is not in the correct place or the sub is not in the correct format...

    Have a look here:

    http://www.wiseowl.co.uk/blog/s194/event-handling-vba.htm

    Andreas.

    Thursday, August 27, 2015 5:59 PM
  • I used exactly the technique the wiseowl blog suggests to create a Workbook_Open() handler within Thisworkbook.  Still, no evidence the code is executed when I open the workbook.  Especially frustrating because I can create a functional handler in a brand new workbook.  I just can't get the handler to work for this pre-existing workbook.  Sigh.
    Thursday, August 27, 2015 7:56 PM
  • Hi,

    This is the forum to discuss questions and feedback for Microsoft Excel, I'll move your question to the MSDN forum for Excel

    http://social.msdn.microsoft.com/Forums/en-US/home?forum=exceldev&filter=alltypes&sort=lastpostdesc

    The reason why we recommend posting appropriately is you will get the most qualified pool of respondents, and other partners who read the forums regularly can either share their knowledge or learn from your interaction with us. Thank you for your understanding.

    George Zhao
    TechNet Community Support


    It's recommended to download and install Configuration Analyzer Tool (OffCAT), which is developed by Microsoft Support teams. Once the tool is installed, you can run it at any time to scan for hundreds of known issues in Office programs.

    Please remember to mark the replies as answers if they help, and unmark the answers if they provide no help. If you have feedback for TechNet Support, contact tnmff@microsoft.com.
    Friday, August 28, 2015 5:38 AM
  • Especially frustrating because I can create a functional handler in a brand new workbook.  I just can't get the handler to work for this pre-existing workbook.  Sigh.

    It is very difficult to guess what's wrong in that file. Please have a look into my profile for my email address and send me your file.

    You can delete confidential data from the sheets if any.

    Andreas.

    Friday, August 28, 2015 10:09 AM
  • Check some other code hasn't disabled events and not re-enabled them, in the Immediate window paste the following and hit enter.

    ?application.enableevents

    Another possibility is the Shift key though that would also prevent Auto_Open, same applies to security settings.

    The auto-macros such as Auto_Open are unlikely to ever be deprecated and work fine and will work even if events are disabled.

    Friday, August 28, 2015 10:26 AM
    Moderator
  • Thanks for reposting George.
    Friday, August 28, 2015 1:45 PM
  • Clearly, it's not some incorrectly set property setting as I had hoped it might be.  It appears to be some strange interaction between the Workbook_Open handler and code in one of the Modules, because the Open code seems to work when I remove all the other modules.  I'll do a bit more troubleshooting and, if I still get nowhere, I'll take you up on your offer to send you the file.

    Thanks,

    cw

    Friday, August 28, 2015 1:49 PM
  • Doubt if it's some other code disabling events since the Open code is my first attempt at using event handlers. 

    Was told that Auto_Open was "obsolete" in another post on another forum.  I agree it would be hard to deprecate.  But thought I'd use the comment as an opportunity to learn how to work with events.

    cw

    Friday, August 28, 2015 1:52 PM
  • What you say about the open event working when other modules are removed makes it seem even more likely some code has disabled events. Just before opening the workbook check EnableEvents as I suggested, if the workbook is being opened with code put a break just before the .Open line and check, then step through with F8.

    BTW are you sure the event is not firing as opposed to not doing what you expect; include this in the first line of the open event and check the Immediate window after

    Debug.Print  ThisWorkbook.Name, "open event"

    Edit: No, Auto_Open is not obsolete even if the idea was once to make it so. It's just that more extensive workbook events were introduced in a later version of Excel, albeit a long time ago now.

    Friday, August 28, 2015 2:12 PM
    Moderator
  • Peter,

    Investigation reveals that the culprit is a function that resides within one of the code modules.  The entire function declaration is as follows:

    Function ThisFolderExists(FolderSpec As String) As Boolean
    Dim fso
    Set fso = CreateObject("Scripting.FileSystemObject")
    ThisFolderExists = fso.FolderExists(FolderSpec)
    End Function

    When the function is coded as above, the Workbook_Open event doesn't even fire when the workbook is opened as evidenced by the lack of a Debug.Print output in the Immediate window as you suggested above.  However, if I comment out all five lines of code above, then the Workbook_Open event fires and its code works as desired.  Strangely, if I leave the function declaration in place and just comment out the three lines of code between Function and End-Function, the Open event STILL does not fire!

    The function is intended to allow a conditional format several cells in a sheet to use a formula similar to "=NOT(ThisFolderExists($B$7))" to change the cell background fill when the folder specified in $B$7 doesn't exist.  When the function is NOT commented out, it (and all other code) works as designed - Except that the Workbook_Open event doesn't fire!  I'm stumped as to why the workbook behaves like that.

    Also, I don't seem to understand how variable scope works since variables defined as global variables within the code modules don't seem to be visible to the Open handler and vice-versa even if the Open handler is not declared as Private.  Is there a way to share variables/data globally between the code modules and the event handlers?

    Thanks for your help.

    cw

    Saturday, August 29, 2015 1:45 PM

  • The function is intended to allow a conditional format several cells in a sheet to use a formula similar to "=NOT(ThisFolderExists($B$7))" to change the cell background fill when the folder specified in $B$7 doesn't exist.


    Wow! That is a crazy idea. :-)

    I have played around a bit and found some interesting results:

    a) The Workbook_Open event does not fire using this function in a conditional formatting:

    Function ThisFolderExists(ByVal FolderSpec As String) As Boolean
    End Function

    b) The Workbook_Open event does fire using this function in a conditional formatting:

    Function ThisFolderExists(ByVal FolderSpec) As Boolean
    End Function

    c) The Workbook_Open event does not fire using this function in a conditional formatting:

    Function ThisFolderExists(ByVal FolderSpec) As Boolean
      ThisFolderExists = Dir(FolderSpec) <> ""
    End Function

    d) The Workbook_Open event does fire using the c) function in a cell and using a conditional formatting with that cell as reference.

    The issue exists in XL2007 and all versions above, but not in XL2003 or below.

    Furthermore any disc operation that is called from a conditional formatting seems to disturb the event handling in Excel. I recommend to remove the formula from the conditional formatting.

    Andreas.
    • Marked as answer by CharlieWright Sunday, August 30, 2015 2:47 PM
    • Unmarked as answer by CharlieWright Sunday, August 30, 2015 2:47 PM
    • Marked as answer by CharlieWright Sunday, August 30, 2015 6:31 PM
    Sunday, August 30, 2015 5:45 AM
  • Wow + 1 :)

    As Andreas says file operations called in CF formulas prevent the open event from firing for some unknown reason, though not other workbook events.

    If (as you say) the open event still fails after commenting the code between the declaration or even the entire function, remove all trace of the formula in CF rules, if necessary clear all rules from all CF sheets and start again.

    Auto_Open is not called indirectly as an "event" but directly, and why it still works in this scenario. I almost added when saying it was not obsolete, some of the pros still use the auto-macros because they work when events might not for reasons beyond their control.

    Also as Andreas suggests, remove the folder check formula from CFs. You could include similar as cell formulas to return a boolean, then apply CF formulas to check the cell values.

    Re-creating the FSO object for each formula call is a mighty sledgehammer for a simple task that can be done with built in functions. Use Dir() as Andreas showed, or my (slight) preference -
    FolderExists = GetAttr(strFolder) And 16

    Sunday, August 30, 2015 10:34 AM
    Moderator
  • Peter,

    Thanks.  I had to give the "Answer" to Andres for his investigation above.  But I ended up using your suggestion for the FolderExists function with the following modification:

    ThisFolderExists = False
    On Error Resume Next
    ThisFolderExists = GetAttr(FolderSpec) And 16

    It returns a Boolean for all cases I've tried, whereas the DIR(FolderSpec) approach that Andreas recommended resulted in #VALUE errors under some circumstances.

    Many thanks BOTH of you for the help here.  As usual, I had thought the issue was something simple, but it turned out to be rather obscure.  Anyway, it's working now and seems to be more robust than my previous implementation - even if I return to using Auto_Open.

    cw

    Sunday, August 30, 2015 6:38 PM