Excel 2013, Thisworkbook.Activate does not ‘stick’ when Application.ScreenUpdating has been set to False

[Matco]

Relates to Excel 2013 only- problem not found in pervious versions of Excel.
' Windows 8.1 V 6.3.9600 Build 9600
' Excel 2013 (15.0.4551.1510) MSO(15.0.4551.1508)32-bit

The simplest way for me to start explaining the problem I am having is the following four line procedure Test().

Sub Test()
Application.ScreenUpdating = False
Workbooks.Open ThisWorkbook.Path & "\Book2.xlsm"
Application.ScreenUpdating = True
ThisWorkbook.Activate
End Sub

ThisWorkbook should be active at the end of the procedure, but it is not.
If I comment out the first line "Application.ScreenUpdating = False", ThisWorkbook is active at the end of the procedure.

The actual code I was running when I first encountered the problem should cause a runtime error if ThisWorkbook.Activate was not working. It does not cause run time error, so it seems ThisWorkbook.activated is working. It just does not 'stick' in that the workbook specified to be active at the end of the procedure is not active. So it is very confusing. I am guessing it is something to do with the SDI Excel 2013 uses.

I have tried setting Application.ScreenUpdating to true before ThisWorkbook.activate and after to no affect. No help from DoEvents or Wait either.
As a work-around I use a MsgBox at the end of the procedure, which for some reason activates ThisworkBook when Application.ScreenUpdating has been set to false

Any help would be greatly appreciated.
Thanks,


The following Sub TestWithComments() provides additional information.

Sub TestWithComments()

'Demonstrates Workbook.Activate does not work when Application.ScreenUpdating = False
' Windows 8.1 V 6.3.9600 Build 9600
' Excel 2013 (15.0.4551.1510) MSO(15.0.4551.1508)32-bit
'Place this procedure in any workbook ' Create a workbook named Book2.xlsm in the same path as the workbook with this code

' If "Application.ScreenUpdating = False" is commented out then _
Thisworkbook is active at the end of the procedure _
Else _
Book2.xlsm is active at the end of the procedure Application.ScreenUpdating = False

'Open Book2 Workbooks.Open ThisWorkbook.Path & "\Book2.xlsm" Application.ScreenUpdating = True ThisWorkbook.Activate

' If "MsgBox ThisWorkbook.Name" is commented out then _
Book2.xlsm is active at the end of the procedure _
Else _
Thisworkbook is active at the end of the procedure 'MsgBox ThisWorkbook.Name

Application.ScreenUpdating = True

End Sub
​

 

[GooberTron]

How do you judge if it is active? Is it just a display / re-draw problem? Or is subsequent code trying to do something to the active workbook? Can you do something like Debug.Print Activeworkbook.name to check which workbook Excel truly thinks is active?

 

[Matco]

After running Test(), I enter "? ActiveWorkbook.name" in the Immediate Window which returns "Book2.xlsm". There is no subsequent code. I am trying to specify the workbook the user should see at the completion of the procedure.

 

[GooberTron]

In 2013,
If I use the following code I get Book1 in the immediate window (however, Book 2 is immediately visible after running the code using Alt-F8 from Book1).

Sub Test()
    Application.ScreenUpdating = False
    Workbooks.Open ThisWorkbook.Path & "\Book2.xlsm"
    Application.ScreenUpdating = True
    ThisWorkbook.Activate
    Debug.Print ActiveWorkbook.Name
End Sub

 

[Matco]

I get the same result. When Debug.Print ActiveWorkbook.name is called, Test() is still running and Book1 is active. When Test() completes (the only remaining command being End Sub) Book2 is active. Something is not right here- do you agree? Thanks for your help.

 

[GooberTron]

Yeah I cannot for the life of me get Book1 to focus cleanly (Msgbox does work though)! The closest I got was displaying a small blank userform and immediately unloading it but unfortunately you still get the split second flicker of the form before Book1 focuses. Hopefully someone else will have a better trick.

 

[RBachman]

Yeah I cannot for the life of me get Book1 to focus cleanly (Msgbox does work though)! The closest I got was displaying a small blank userform and immediately unloading it but unfortunately you still get the split second flicker of the form before Book1 focuses. Hopefully someone else will have a better trick.

I have found something that works, with some negative consequences

Sub Test1()
'
' Excel 2013 Problem only
' Test1 returns the correct state for Thisworkbook
' Negative consequences after macro has run:
' View, Arrange all (any pick), does not show minimized workbooks
'
Application.ScreenUpdating = False
Workbooks.Open ThisWorkbook.Path & "\Book2.xlsm"
Windows("Book2.xlsm").WindowState = xlMinimized ' This line has been added
Application.ScreenUpdating = True
ThisWorkbook.Activate
End Sub