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

Matco

New Member
Joined
Feb 18, 2014
Messages
3
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

Board Regular
Joined
Oct 13, 2012
Messages
205
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

New Member
Joined
Feb 18, 2014
Messages
3
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

Board Regular
Joined
Oct 13, 2012
Messages
205
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).

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

Matco

New Member
Joined
Feb 18, 2014
Messages
3
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

Board Regular
Joined
Oct 13, 2012
Messages
205
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

New Member
Joined
Jul 1, 2012
Messages
3
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
 

Forum statistics

Threads
1,081,615
Messages
5,360,037
Members
400,565
Latest member
Tommy O

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top