+ Reply to Thread
Results 1 to 5 of 5

Opens another workbook but doesn't activate it, ignores ScreenUpdating = False

Hybrid View

  1. #1
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Opens another workbook but doesn't activate it, ignores ScreenUpdating = False

    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
    Acts 4:12
    Salvation is found in no one else, for there is no other name under heaven given to mankind by which we must be saved.

  2. #2
    Forum Expert
    Join Date
    12-14-2012
    Location
    London England
    MS-Off Ver
    MS 365 Office Suite.
    Posts
    8,448

    Re: Opens another workbook but doesn't activate it, ignores ScreenUpdating = False

    I suspect that "thisworkbook" refers to the workbook housing the macro.

    try
    windows(workname).activate
    My General Rules if you want my help. Not aimed at any person in particular:

    1. Please Make Requests not demands, none of us get paid here.

    2. Check back on your post regularly. I will not return to a post after 4 days.
    If it is not important to you then it definitely is not important to me.

  3. #3
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Opens another workbook but doesn't activate it, ignores ScreenUpdating = False

    Thanks for the suggestion. Actually, it would be
    Windows(ThisWorkbook.Name).Activate
    I tried it, but with the same results.

  4. #4
    Forum Guru Izandol's Avatar
    Join Date
    03-29-2012
    Location
    *
    MS-Off Ver
    Excel 20(03|10|13)
    Posts
    2,581

    Re: Opens another workbook but doesn't activate it, ignores ScreenUpdating = False

    ScreenUpdating will be made True when first routine ends and before second routine is run. This is normal and same as previous versions.
    • Please remember to mark threads Solved with Thread Tools link at top of page.
    • Please use code tags when posting code: [code]Place your code here[/code]
    • Please read Forum Rules

  5. #5
    Valued Forum Contributor natefarm's Avatar
    Join Date
    04-22-2010
    Location
    Wichita, Kansas
    MS-Off Ver
    2016
    Posts
    1,020

    Re: Opens another workbook but doesn't activate it, ignores ScreenUpdating = False

    Based on that clue, I tried the following adjustment
    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.Wait (Now + #12:00:01 AM#)
        Windows(ThisWorkbook.Name).Activate
    End Sub
    but it also didn't work. It pauses for 1 second, but leaves Lookups as the active workbook.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 1
    Last Post: 09-30-2013, 10:56 PM
  2. Combobox doesn't activate / load when workbook is opened
    By asgersax in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-19-2012, 06:01 AM
  3. Activate the open workbook (that doesn't contain the code!)
    By seaveegee in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 09-19-2012, 10:23 AM
  4. Replies: 0
    Last Post: 02-07-2006, 01:50 AM
  5. What does ScreenUpdating = False do?
    By Dave Peterson in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 09-06-2005, 06:05 AM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1