Screenupdate vs Selection issue - VBA Excel

Question

I'm facing an odd situation. I have a button on a sheet which runs many functions, being one of those opening another file:

        If Not IsItOpen(ENDERECO2) Then
            Workbooks.Open Filename:=ENDERECO1
        End If

        'ENDERECO2 has the file's name
        'ENDERECO1 has the full path of the same file
        'IsItOpen is a private function as follows:
        'Private Function IsItOpen(Name As Variant) As Boolean
        '    On Error Resume Next
        '    IsItOpen = Not (Application.Workbooks(Name) Is Nothing)
        'End Function

After opening the other workbook, when it isn't already opened, I bring focus to the first sheet, as I want the second one to be opened on the background. To do that, I use:

        'At the very beggining of the code
        Dim CEL As Range
        Set CEL = Selection 
        'And at the end of it all
        CEL.Select

All the described code works perfectly. The problem I've been having: as this button runs many things at once, I wanted to add an "Application.Screenupdating = False" at the beggining and "... = True" at the end, so it won't flicker too much when calculating. The thing is that when I added the Screenupdating stuff, it will still open the second workbook as desired, but it won't bring the focus back to the main workbook. Instead, it stops at the recently opened workbook and there it stays. What could be the interference of the Screenupdating on the CEL.Select command? Any ideas? Cheers

Using Selection is normally frowned upon and in some cases is asking for trouble - see How to avoid using Select in Excel VBA. You can Activate the first sheet - ThisWorkbook.Worksheets("Yoursheetname").Activate, for example. — BigBen
Tried it but it won't work. I guess once the new workbook is opened, then it becomes "Thisworkbook". Tried also "Workbooks(filename).worksheets("sheetname).activate" but it keeps staying at the new opened file — Guilherme Nascimento
ThisWorkbook refers to the workbook in which the code resides. ThisWorkbook never changes in the same module/sheet's code. What will change when another sheet is activated is ActiveWorkbook. I personally find ActiveWorkbook to be similarly as dangerous as Selection and try to avoid is as much as possible. — Taelsin
@GuilhermeNascimento Can you try setting ScreenUpdating to true immediately before activating your first workbook? After that you can set ScreenUpdating back to false to speed up your calculations. — Taelsin

Guilherme Nascimento Guilherme Nascimento · Accepted Answer · 2018-08-07T17:26:17

Thanks Taelsin. Guess when we don't know exactly why, we improvise lol. This worked fine:

    If Not IsItOpen(ENDERECO2) Then 
       Application.ScreenUpdating = True 
       Workbooks.Open Filename:=ENDERECO1 
       Application.ScreenUpdating = False 
    End If

It is good enough. Cheers!

Screenupdate vs Selection issue - VBA Excel

1 Answers