Screenupdate vs Selection issue - VBA Excel

The name of the pictureThe name of the pictureThe name of the pictureClash 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





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
Aug 7 at 15:19


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.

-

Popular posts from this blog

Firebase Auth - with Email and Password - Check user already registered

Image
Clash Royale CLAN TAG #URR8PPP Firebase Auth - with Email and Password - Check user already registered I want to check when a user attempts to signup with createUserWithEmailAndPassword() in Firebase user Authentication method, this user is already registered with my app. createUserWithEmailAndPassword() 8 Answers 8 To detect whether a user with that email address already exists, you can detect when the call to createUserWithEmailAndPassword () fails with auth/email-already-in-use . I see that @Srinivasan just posted an answer for this. createUserWithEmailAndPassword () auth/email-already-in-use Alternatively, you can detect that an email address is already used by calling fetchSignInMethodsForEmail() (previous called fetchProvidersForEmail() ). fetchSignInMethodsForEmail() fetchProvidersForEmail() When the user trying to create an user with same email address, the task response will be "Response: The email address is already in use by another account." mFirebas
Read more

Dynamically update html content plain JS

Image
Clash Royale CLAN TAG #URR8PPP Dynamically update html content plain JS So I have a script bound in my index.html, which literally has to fetch an array of jsons from an api, then dynamically update my page when get response. But as a result I'm getting an as the only update of my page. here is my script fetch(url) .then((resp) => resp.json()) .then((resp) => resp.results.forEach(item => fetch(item.url) .then((response)=> response.json()) .then((response) => pokeArr.push(response)) .catch(console.log) ) ).catch(console.log) const filler = () => if(!pokeArr) return 0 pokeArr.map((i,j)=> return `<tr>$i.name</tr>` ) const pokeindex = () => document.getElementById('a').appendChild(document.createElement(filler())) pokeindex() When I'm consoling it, I can see in the console all the responses I get, so I'm at least doing the fetching part right. What's parberakan ? What's pokeArr ? – T.J. Crowder Aug 8 at 12
Read more

How to determine optimal route across keyboard

Image
Clash Royale CLAN TAG #URR8PPP How to determine optimal route across keyboard I'm trying to automate the typing process in a video game. Players are given an on-screen QWERTY keyboard layout that they can navigate with the control stick (up, down, left, right) in order to select a letter to type. I was wondering if there was a module for Python (or some other resource I could explore) that would help my program find a path across the keyboard as it types each letter in a given message. In other words, if the first letter typed was "A" and the next letter was "B", the module would know that the program should move 1 space down on the keyboard and 4 spaces to the right. Currently, I have the program resetting to the "G" key after each letter is typed (allowing me to use fixed-directions as the program parses each character in the desired message). As you can imagine, this is quite inefficient and defeats the purpose of automating the typing in the fir
Read more