Consulting

Results 1 to 12 of 12

Thread: Workbook_Open operates inconsistenly

  1. #1
    VBAX Regular
    Joined
    Jun 2017
    Posts
    10
    Location

    Workbook_Open operates inconsistenly

    Hello -

    I have a simple Workbook_Open macro (in the ThisWorkbook object) as follows:

    Private Sub Workbook_Open()
    
    ' Ensure that calculations and screen updating are ON
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
    
    
    ' Leave workbook in working state if last save was in debug mode
        If Range("DEBUG_FLAG").Value = False Then
            Call Set_Defaults
        End If
    
    
    ' Default Migrate Flag to false so that regular processing can commence
        Range("MIGRATE_FLAG").Value = False
    
    
    End Sub


    I get inconsistent behavior when opening the workbook. Normally on the first open attempt, the Workbook_Open macro does not run. I know this because the Set_Defaults macro that is called within Workbook_Open does not run. However, on the second attempt, the macro runs. Has anyone seen this before? Thanks in advance for any thoughts on a remedy.

  2. #2
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    CHeck and see if something it setting Application.EnableEvents = False and leaving it that way
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  3. #3
    VBAX Expert Dave's Avatar
    Joined
    Mar 2005
    Posts
    832
    Location
    U haven't identified what sheet the range relates to? U also haven't indicated what the set defaults macro does or what happens on workbook close and/or save? The Range("DEBUG_FLAG").Value is not a Boolean (which would be auto false on wb open) so chances are it equals vbnullstring on wb open until set to false. HTH. Dave

  4. #4
    VBAX Regular
    Joined
    Jun 2017
    Posts
    10
    Location
    Quote Originally Posted by Paul_Hossler View Post
    CHeck and see if something it setting Application.EnableEvents = False and leaving it that way
    Thank you, Paul. But when you first open a workbook, isn't Application.EnableEvents = True always, or does it retain the setting from when last saved?

  5. #5
    VBAX Regular
    Joined
    Jun 2017
    Posts
    10
    Location
    Quote Originally Posted by Dave View Post
    U haven't identified what sheet the range relates to? U also haven't indicated what the set defaults macro does or what happens on workbook close and/or save? The Range("DEBUG_FLAG").Value is not a Boolean (which would be auto false on wb open) so chances are it equals vbnullstring on wb open until set to false. HTH. Dave
    Thank you, Dave. But I'm still not following why the code executes when I open the workbook the second time, without having made any changes or saving the workbook when I immediately exit the first opening of the workbook. FWIW, Set_Defaults pre-fills some cells based on values from the last save, and Range("DEBUG_FLAG") contains a boolean value.

  6. #6
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by dhregan View Post
    Thank you, Paul. But when you first open a workbook, isn't Application.EnableEvents = True always, or does it retain the setting from when last saved?

    When Excel opens, Application.EnableEvents = True, but if

    1. WB #1 sets Application.EnableEvents = False

    2. WB#1 is closed, but Excel is not

    3. WB#2 opened (e.g. File, Open) then the Application.EnableEvents status is persistent


    Example:

    a. Open Excel

    b. File Open WB1.xlsm - WB Open files with message, and turns EnableEvents off

    c. File Close WB1.xlsm, but leave Excel Open
    .
    d. File Open WB2.xlsm - WB Open event does not file

    e. Close Excel

    f. Open Excel and Open WB2 - WB Open fires


    I always turn off EnableEvent, do only what might cause an event handler to be called, and turn it on again -- usually within 2-3 lines, but always in the same sub



    Attached Files Attached Files
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  7. #7
    VBAX Regular
    Joined
    Jun 2017
    Posts
    10
    Location
    Quote Originally Posted by Paul_Hossler View Post
    When Excel opens, Application.EnableEvents = True, but if

    1. WB #1 sets Application.EnableEvents = False

    2. WB#1 is closed, but Excel is not

    3. WB#2 opened (e.g. File, Open) then the Application.EnableEvents status is persistent


    Example:

    a. Open Excel

    b. File Open WB1.xlsm - WB Open files with message, and turns EnableEvents off

    c. File Close WB1.xlsm, but leave Excel Open
    .
    d. File Open WB2.xlsm - WB Open event does not file

    e. Close Excel

    f. Open Excel and Open WB2 - WB Open fires


    I always turn off EnableEvent, do only what might cause an event handler to be called, and turn it on again -- usually within 2-3 lines, but always in the same sub



    Thanks again, Paul. I should have clarified in my original post that between open attempt #1 and open attempt #2, I am closing Excel entirely without saving the file.

  8. #8
    If you put the word Stop at the very beginning of your open event.
    Then close & open the workbook as few times, does the VBE open & highlight the line everytime you open the workbook?

  9. #9
    VBAX Sage
    Joined
    Apr 2007
    Location
    United States
    Posts
    8,711
    Location
    Quote Originally Posted by dhregan View Post
    Thanks again, Paul. I should have clarified in my original post that between open attempt #1 and open attempt #2, I am closing Excel entirely without saving the file.

    Are there any add-ins, Personal.xlsm, or other hidden workbooks (XLSTART folder) that might be affecting it?


    Try Excel safe mode

    https://excelribbon.tips.net/T011632...Safe_Mode.html

    Start, Run excel.exe /s
    ---------------------------------------------------------------------------------------------------------------------

    Paul


    Remember: Tell us WHAT you want to do, not HOW you think you want to do it

    1. Use [CODE] ....[/CODE ] Tags for readability
    [CODE]PasteYourCodeHere[/CODE ] -- (or paste your code, select it, click [#] button)
    2. Upload an example
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) / Upload Files / Done
    3. Mark the thread as [Solved] when you have an answer
    Thread Tools (on the top right corner, above the first message)
    4. Read the Forum FAQ, especially the part about cross-posting in other forums
    http://www.vbaexpress.com/forum/faq...._new_faq_item3

  10. #10
    VBAX Regular
    Joined
    Jun 2017
    Posts
    10
    Location
    Quote Originally Posted by Fluff View Post
    If you put the word Stop at the very beginning of your open event.
    Then close & open the workbook as few times, does the VBE open & highlight the line everytime you open the workbook?
    Hello Fluff -

    I don't know what changed, but after looking into the items that Paul noted in post 9, the problem appears to have gone away. Nonetheless, I inserted the Stop statement and was able to verify that the Workbook_Open macro is firing. Thanks for your suggestion.

  11. #11
    VBAX Regular
    Joined
    Jun 2017
    Posts
    10
    Location
    Quote Originally Posted by Paul_Hossler View Post
    Are there any add-ins, Personal.xlsm, or other hidden workbooks (XLSTART folder) that might be affecting it?


    Try Excel safe mode

    https://excelribbon.tips.net/T011632...Safe_Mode.html

    Start, Run excel.exe /s
    Hello Paul -

    I confirmed that there are no active add-ins, there is no personal.xlsm and that XLSTART is empty. I also opened the workbook in safe mode. After all of these things, the problem seems to have disappeared. I didn't change anything, so I don't know what made the problem go away. Thanks for your suggestions.

  12. #12
    Glad it's sorted & thanks for the feedback

Posting Permissions

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