I updated a .xls workbook to .xlsm. It automatically opens another workbook, Lookups.xlsx, containing a bunch of lookup tables used in numerous combo boxes and formulas.
When I open the 1st workbook, Lookups.xlsx becomes and remains the active workbook, even though the code does a ThisWorkbook.Activate. The same is true if I run Auto_Open manually. Here is the original code:
Option Explicit
Dim WorkName As String, ReadOption As Boolean
Dim WorkPath As String
Sub Auto_Open()
Application.ScreenUpdating = False
WorkPath = Range("PrimaryPath").Value
WorkName = Range("LookupName").Value
ReadOption = Range("ReadOnly").Value
Workbooks.Open Filename:=WorkName, ReadOnly:=ReadOption
ThisWorkbook.Activate
Application.ScreenUpdating = True
End Sub
I tried stepping through it with the debugger. It opens Lookups, but when I hit F8 for the next step, nothing happens because the Lookups window now has the focus. If I click the debug window to put the focus back there, and then hit F8, it activates ThisWorkbook as it should.
I suspected some kind of timing issue, so I changed the code to the following:
Sub Auto_Open()
WorkPath = Range("PrimaryPath").Value
WorkName = Range("LookupName").Value
ReadOption = Range("ReadOnly").Value
Application.ScreenUpdating = False
Workbooks.Open Filename:=WorkName, ReadOnly:=ReadOption
Application.OnTime Now() + TimeValue("00:00:01"), "ActivateMe"
End Sub
Sub ActivateMe()
ThisWorkbook.Activate
Application.ScreenUpdating = True
End Sub
This solved the activation issue, but it ignores ScreenUpdating = False. This was not the case in previous versions of Excel. Does 2007/2010/2013 ignore ScreenUpdating = False when opening another workbook?
I've presented two different issues here. It would be nice to know the answers to both, but I'd settle for either one
Bookmarks