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](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). You can `Activate` the first sheet - `ThisWorkbook.Worksheets("Yoursheetname").Activate`, for example. — BigBen, Aug 07 '18 at 15:19
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, Aug 07 '18 at 15:37
`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, Aug 07 '18 at 15:38
@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, Aug 07 '18 at 15:43

score 1 · Answer 1 · answered Aug 07 '18 at 17:26

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 Answers1