Screenupdate vs Selection issue - VBA Excel
Clash Royale CLAN TAG#URR8PPP
Screenupdate vs Selection issue - VBA Excel
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
Selection
Activate
ThisWorkbook.Worksheets("Yoursheetname").Activate
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 7 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 7 at 15:38
ThisWorkbook
ThisWorkbook
ActiveWorkbook
ActiveWorkbook
Selection
@Taelsin thank you, beat me to it.
– BigBen
Aug 7 at 15:39
@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 7 at 15:43
ScreenUpdating
ScreenUpdating
1 Answer
1
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!
By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.
Using
Selectionis normally frowned upon and in some cases is asking for trouble - see How to avoid using Select in Excel VBA. You canActivatethe first sheet -ThisWorkbook.Worksheets("Yoursheetname").Activate, for example.– BigBen
Aug 7 at 15:19