none
Excel 2013 screenupdating issue with multiple workbooks RRS feed

  • Question

  • It appears that some changes were made in Excel 2013 to the process involved when opening or creating new workbooks.  This change is causing problems with VBA that relate to screenupdating because of the changing focus.  In 2007/2010 if screenupdating = false, any workbooks opened or created will be visibly hidden behind whatever was showing prior to turning screenupdating off.  In Excel 2013, the new workbook window will visibly overlap the initial workbook and any frozen view.  It will remain blank, but it will cover the initial workbook and any forms that were open.  This new focus also seems to affect other code created for use with previous versions of Excel.  What was considered the Active workbook when other workbooks are opened or closed reacts differently in 2013.  Even with screenupdating = false any changes in the "active" workbook will cause the currently active workbook to display on top causing flicker or covering userforms etc.  Also in 2013, if a form is opened immediately after opening a second workbook, that form will automatically close when the second workbook is closed. In this case UserForm_QueryClose  event will give a CloseMode value of 5.  There does not appear to be a documented value of 5 for CloseMode, however, VB6 has an UnloadMode value of 5 that refers to closing the form because the "owner" has closed.  This seems to fit the scenario described, but this did not happen in 2010.  I am using windows 8 and Excel 2013 64bit.  To ask a question; How can I prevent a newly opened workbook or "active" workbook from displaying on top of the primary workbook and userform?
    Friday, September 19, 2014 2:26 AM

All replies

  • I think you may be confusing the purpose of Application.ScreenUpdating with Application.Visible...

    Even with 2010 If I run a simple sub

    Sub Main()
    Application.ScreenUpdating = False
    
        Dim wb As Workbook
        Set wb = Application.Workbooks.Add
    
    End Sub
    

    The newly added Book2 will stay on top because the Sub itself after finishing running will implicitly turn the .ScreenUpdating to True.

    Just forget the 2010 vs. 2013 issue for now. I think your objects may not have been properly qualified. If I was you I would go over the code and make sure that different workbooks are properly qualified. Make sure you understand the purpose of .Visible property vs. .SceenUpdating

     

    Friday, September 19, 2014 7:35 AM
  • Hi,

    >>How can I prevent a newly opened workbook or "active" workbook from displaying on top of the primary workbook and userform?<<

    As far as I know, when we open a workbook, the workbook is active Workbook. And the active workbook always display on the top of the workbooks by default.

    If you want to show some workbook, we can active the specific workbook, or we can hide the window of active workbook.

    Here is a sample to hide the active workbook for your reference:

    Sub HideWindow()
     Application.ActiveWorkbook.Windows(1).Visible = False
     
    End Sub

    Best regards

    Fei


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, September 26, 2014 9:47 AM
    Moderator
  • Indeed there are significant changes in 2013 with the introduction of SDI (previously MDI). When your code adds or opens a workbook it opens in a new application window with its own ribbon, the behaviour is similar to how Word has always been.

    Depending on what you are doing you might be able to get away with simply toggling the visible state of 'your' application after adding the new workbook to bring it back in focus, eg

    Set wb = Workbooks.Add
    Application.Visible = False
    Application.Visible = True
    AppActivate Me.Caption

    However for much better control you need APIs, good example and explanation here

    http://www.jkp-ads.com/Articles/keepuserformontop01.asp


    Friday, September 26, 2014 1:38 PM
    Moderator