-
Mar 28th, 2007, 03:07 PM
#1
Thread Starter
Addicted Member
Application.ScreenUpdating = false does not work
I have a procedure that runs:
Code:
Application.ScreenUpdating = false
which obviously isn't working because the screen updates a whole lot afterwards. I started to debug, and immediately after the "ScreenUpdating = false" line executes, I go to the immediate window and type:
Debug.Print Application.ScreenUpdating
and Excel responds with "True". I know Excel ran "Application.ScreenUpdating = false" because I saw the yellow highlight go over the line in debug mode. Yet, the status of Application.ScreenUpdating remains true.
However, I *can* change the ScreenUpdating from the immediate window. I just can't set it from within a procedure.
Why would this be?
-
Mar 28th, 2007, 03:24 PM
#2
Fanatic Member
Re: Application.ScreenUpdating = false does not work
Rather than go into debug mode and type into the immediate window, you might want to add:
Code:
Debug.Print Application.ScreenUpdating
immediately after the the line that turns screen updating off. That way you can be sure if it is working or not. If it is, then something must be turning it back on...
-
Mar 28th, 2007, 03:32 PM
#3
Re: Application.ScreenUpdating = false does not work
which obviously isn't working because the screen updates a whole lot afterwards.
May I see the exact code if it not too big?
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Mar 28th, 2007, 04:23 PM
#4
Thread Starter
Addicted Member
Re: Application.ScreenUpdating = false does not work
Sure. Unfortunately, I suspect it won't be too illuminating. The application is pretty big and has many interacting parts. But here's the part of the code that this is happening in:
Code:
Sub loadPortfolio()
Dim iTest As Integer, portfolioVersion As String
Application.ScreenUpdating = False
bGlobalChangeEvent = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = False
Application.ScreenUpdating = False
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.ScreenUpdating = False
Application.ScreenUpdating = False
If myDebug = False Then On Error GoTo ErrorHandler
I'm afraid that's pretty much it. Well, the function is quite long, but you're looking at the function's entry point. All those ScreenUpdating = False's you see is called "sheer desperation". :-)
Basically, after all those statements (and between each one) ScreenUpdating remains "True".
About to go home for the day, but I plan on trying to print Application.ScreenUpdating from within the code instead of the immediate window, but I'm fairly sure what I'll find. Still, it never hurts to try!
This function gets called by an event handler that catches a CommandsBar button event (?) I'm still learning the terminology. Basically, we create a menu bar and one of the buttons in our custom "File" menu is labeled "Load Portfolio" which calls this function.
I almost wish this were more complicated; the fact that everything looks so simple is making it difficult to think of what could possibly be happening!
Thanks!
Pete
-
Mar 28th, 2007, 04:35 PM
#5
Re: Application.ScreenUpdating = false does not work
The code is not much of help as I don't see it updating anything...
also try this...
vb Code:
Application.Calculation = xlCalculationManual
and then change it to Automatic at the end of the code...
Last edited by Siddharth Rout; Mar 28th, 2007 at 04:38 PM.
Reason: Added VB Tags
A good exercise for the Heart is to bend down and help another up...
Please Mark your Thread "Resolved", if the query is solved
MyGear:
★ CPU ★ Ryzen 5 5800X
★ GPU ★ NVIDIA GeForce RTX 3080 TI Founder Edition
★ RAM ★ G. Skill Trident Z RGB 32GB 3600MHz
★ MB ★ ASUS TUF GAMING X570 (WI-FI) ATX Gaming
★ Storage ★ SSD SB-ROCKET-1TB + SEAGATE 2TB Barracuda IHD
★ Cooling ★ NOCTUA NH-D15 CHROMAX BLACK 140mm + 10 of Noctua NF-F12 PWM
★ PSU ★ ANTEC HCG-1000-EXTREME 1000 Watt 80 Plus Gold Fully Modular PSU
★ Case ★ LIAN LI PC-O11 DYNAMIC XL ROG (BLACK) (G99.O11DXL-X)
★ Monitor ★ LG Ultragear 27" 240Hz Gaming Monitor
★ Keyboard ★ TVS Electronics Gold Keyboard
★ Mouse ★ Logitech G502 Hero
-
Jul 18th, 2008, 04:42 PM
#6
New Member
Re: Application.ScreenUpdating = false does not work
Did you ever find a solution to this problem? I have encountered the same symptoms and can't think as to why it's happening.
So this morning application.screenUpdating was working as expected. And this afternoon it stopped working. I ran out of things to try. I think that maybe something has broken in my excel because even when I closed out of excel and opened a brand new 2003 workbook with no previous code I was getting the same problem. The test code that I wrote is below:
Sub test()
Debug.Print Application.ScreenUpdating
Application.ScreenUpdating = False
Debug.Print Application.ScreenUpdating
Application.ScreenUpdating = True
Debug.Print Application.ScreenUpdating
End Sub
As I step through the code when I hove over Application.screenUpdating it always says True. The immediate window says True, then False, then True again as expected, but the code doesn't seem to have any effect on the application. I can still enter the workbook and it is screenupdating away even right after the line set it false.
If is execute the line "Application.ScreenUpdating = False" in the immediate window it works as expected.
Any help would be great. Thanks
Colin
-
Oct 2nd, 2008, 03:56 AM
#7
New Member
Re: Application.ScreenUpdating = false does not work
Hi
Did anyone ever find a solution to this problem? I'm having exactly the same issue.
Any news would be greatly appreciated.
Thanks.
-
Feb 8th, 2009, 05:21 PM
#8
New Member
Re: Application.ScreenUpdating = false does not work
Is it possible that ScreenUpdating=false does not take effect in debug mode?
-
Feb 9th, 2009, 06:26 AM
#9
Re: Application.ScreenUpdating = false does not work
Correct, when you step through in debug mode the screenupdating remains turned on. At least, it does in my version of Excel.
To test it properly, try this:
Put a commandbutton on your sheet and paste in
Code:
Application.ScreenUpdating = False
For i = 1 To 10000
Worksheets("Sheet1").Range("A1").Offset(i, 0).Value = i
Worksheets("Sheet1").Range("A1").Offset(i, 0).Activate
Next i
Application.ScreenUpdating = True
Try running it and you should see a short pause followed by column A getting filled with numbers.
Then comment out the line which turns off screen updating and see what happens; you should find that you scroll down the sheet as the numbers get filled.
Then put it in debug mode and step through with screen updating turned off again, you should find that you still see the numbers going in one by one.
If all of this happens, then you know it is working correctly.
-
Oct 6th, 2009, 02:48 AM
#10
New Member
Re: Application.ScreenUpdating = false does not work
I've experienced this problem, with code that previously worked! Screenupdating = False worked perfectly in Excel 2007 until last week, when it remained True however many times I tried to set it to False.
So while ScreenUpdating always is True in debug mode, this is not the issue for me.
The interesting thing is that *some* of my modules allow ScreenUpdating to be set to False, there's only a few that don't. One thing that seems to be common among those modules not working, is that they all contain code that changes worksheets. Could it be related to the Select function?
In any case, it's a real mystery why this happens from one day to another! Any clues on how to solve it would be greatly appreciated.
-
Dec 15th, 2009, 08:45 AM
#11
New Member
Re: Application.ScreenUpdating = false does not work
Application.screenupdating = False was not stopping the screen update when it was call straight in an event.
In my case, when adding Application.screenupdating = False in my Workbook_BeforeSave code, it did not do it's job.
When I moved the code I wanted to run before save to it's own module, then called the module from Workbook_BeforeSave, it worked like a charm...(events disabled in module, so additional sheet level events did not trigger)
Hoping this will help someone else out - drove me crazy for a while!!!
Last edited by G-Ma0801; Dec 15th, 2009 at 08:57 AM.
Reason: additional info
-
Mar 18th, 2010, 07:42 AM
#12
New Member
Re: Application.ScreenUpdating = false does not work
A common reason why this problem occurs is when your code calls for code in a different procedure. The Application.ScreenUpdating is turned on outside of the procedure. Check other procedures that are called within your current one to check if they are changing your Application.ScreenUpdating to True.
Here is a sample of what could be happening.
________________________________________________________
Private Sub Clock_In()
Application.ScreenUpdating = False
Label10.Caption = "Ready to clock out of last task."
CommandButton2.Enabled = True
Check_for_Open_Workbook ' ScreenUpdating is set back to True
Sheets("Time Log").Select
Range("B2").Value = ComboBox1.Text
Sheets("Time Log").Select
Application.ScreenUpdating = True
End Sub
_________________________________________________________
Private Sub Check_for_Open_Workbook()
Application.ScreenUpdating = False
On Error GoTo Open_Workbook1
Windows("Time M.xls").Activate
GoTo Ending
Ending:
Application.ScreenUpdating = True ' Here is the error
End Sub
_________________________________________________________
Hope that helps somebody!
-
May 24th, 2010, 08:59 PM
#13
New Member
Re: Application.ScreenUpdating = false does not work
I have had this same problem... yes a common occurrence is when you call another piece of code.
I was killing myself over why the heck screen updating was not set to false!!!! It was eating at me!!
I solved my problem with a very simple solution:
At the very very end of the code put: Application.Screenupdating = True.
I know it sounds stupid - but it worked for me. Just double check that you have that.
-
Nov 10th, 2012, 02:54 PM
#14
New Member
Re: Application.ScreenUpdating = false does not work
I ran into this problem a few times myself recently. In one particular instance I found an interesting fix: I had a Watch set. I was watching the state of an object within the Workbook (the locked property of a specific style). no matter what I did in code to turn off screen updating, it would not work and my app slowed to a crawl. However, as soon as I removed the watch, it worked fine. So keep an eye out for that.
-
Jul 5th, 2013, 02:49 AM
#15
New Member
Re: Application.ScreenUpdating = false does not work
Originally Posted by tjeffryes
I ran into this problem a few times myself recently. In one particular instance I found an interesting fix: I had a Watch set. I was watching the state of an object within the Workbook (the locked property of a specific style). no matter what I did in code to turn off screen updating, it would not work and my app slowed to a crawl. However, as soon as I removed the watch, it worked fine. So keep an eye out for that.
I undig this post because I just ran into this issue myself on Excel 2007! Thanks to tjeffryes' remark, I checked if I had any watch set and I didn't... I re-insist as well on the fact the "NO there was no call to another sub procedure setting it to "True" because when debugging my procedure, I could clearly see the value of "Application.ScreenUpdating" not changing from True to False (thus being TRUE before and staying to TRUE right after the "Application.ScreenUpdating = False" statement) while the "Application.EnableEvents" was changing from True to False, so all the people who say that in debug mode it never changes, this is not right to say that! Yes indeed, the screen continues to be updated so that you can see the changes happen, but the value of this variable DOES CHANGE effectively.
Now, have I solved my issue? YES
I remember that when I brought the last change to my code, I had indeed set a watch, but since I am working on customizing the ribbon, I save and close very often my excel file and I have certainly not deleted all the watches before posting my updates to prod. Hence, what I did is to follow tjeffryes' recommendation and created a new watch on any variable. I set it to "All procedures" and "All modules" for the context, ran the procedure as usual in debug mode, then deleted the watch, closed the VB Code editor, and it was back to normal, the screen not being updated as my code was stating properly!
I really hope this will help some other people coming here for the same issue since indeed, this is the kind of things very time consuming and going you crazy!
-
Jul 5th, 2013, 08:43 PM
#16
New Member
Re: Application.ScreenUpdating = false does not work
I'm very glad it helped someone!
-
Jul 8th, 2013, 11:01 AM
#17
New Member
Re: Application.ScreenUpdating = false does not work
Originally Posted by (/iropracy
I have had this same problem... yes a common occurrence is when you call another piece of code.
I was killing myself over why the heck screen updating was not set to false!!!! It was eating at me!!
I solved my problem with a very simple solution:
At the very very end of the code put: Application.Screenupdating = True.
I know it sounds stupid - but it worked for me. Just double check that you have that.
I was able to solve my issue by deleting the Application.Screenupdating = True line at the end of my code. It appears that there is no single solution to this problem. Each person may be experiencing it for a different reason. Just try everything on this page and see if it works for you!
-
Oct 9th, 2013, 03:11 PM
#18
New Member
Re: Application.ScreenUpdating = false does not work
Although I'm using VBA, not VB, I experienced the same symptoms when using Application.ScreenUpdating = False
, and this is how I solved it:
I found that Application.ScreenUpdating behaves as a procedure-level application property. As soon as my code leaves the procedure where Application.ScreenUpdating = False was set, it's value resets to True. To solve, for each procedure, and called procedures, that I wanted to run with ScreenUpdating set to False, I "bookended" my procedure and its called procedures with the ScreenUpdating code at the top and bottom of each procedure. For example:
Sub Main_Procedure
Application.ScreenUpdating = False
Call Secondary_Procedure
Application.ScreenUpdating = True
End Sub
******
Sub Secondary_Procedure
Application.ScreenUpdating = False
...code runs...
Application.ScreenUpdating = True
End Sub
-
Oct 26th, 2013, 03:27 AM
#19
New Member
Re: Application.ScreenUpdating = false does not work
I am having the same problem. I have a macro that reads info from one workbook and updates another. As the macro swaps from one to the other on my Mac it shows where the focus is so I see the 2 worksheets swapping but if I run the exact same macro on a PC it doesn't. Screen updating is set to false at the start and to true at the end only with no sub routines called but I do call functions
-
Oct 27th, 2013, 05:34 AM
#20
Re: Application.ScreenUpdating = false does not work
I have a macro that reads info from one workbook and updates another. As the macro swaps from one to the other
there is no reason to move selection or activate each workbook, just read and write fully qualified ranges
activating workbooks or sheets, or selecting ranges in macros is bad practice and leads to errors in results and other issues
AFAIK the is never any need to set application.screenupdating to true, as this will happen automatically when the code finishes
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Oct 27th, 2013, 07:08 AM
#21
New Member
Re: Application.ScreenUpdating = false does not work
it is something that I inherited and has about 2k lines in total adding info from 3 sources so I don't want to rewrite it (and yes there are many functions to reduce it to that number). IS there a way of stopping it from doing this on a Mac to save me that pain?
Thanks for the info about setting it to True again
-
Oct 27th, 2013, 02:56 PM
#22
Re: Application.ScreenUpdating = false does not work
IS there a way of stopping it from doing this on a Mac to save me that pain?
i have no idea, i never use Mac, if the code was well written it would probably not be an issue
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
-
Oct 15th, 2014, 08:20 PM
#23
New Member
Re: Application.ScreenUpdating = false does not work
I've had this problem and it has puzzled me for several days.
It appears that if you declare global variables in a separate module, then when such variables are used the application.screenupdating resets to true. The 'trip' to look for the global variable has acted the same as calling another procedure, which is one point raised by several earlier posts.
My solution was to make screenupdating false immediately after using the global variables or constants.
Hope that this might help others.
-
Jul 11th, 2015, 04:42 PM
#24
New Member
Re: Application.ScreenUpdating = false does not work
Originally Posted by westconn1
there is no reason to move selection or activate each workbook, just read and write fully qualified ranges
activating workbooks or sheets, or selecting ranges in macros is bad practice and leads to errors in results and other issues
AFAIK the is never any need to set application.screenupdating to true, as this will happen automatically when the code finishes
I found this to be true. My solution was to comment out all of my "application.screenupdating to true" at the end of each sub which were many, which called other subs....worked great. Thank you..it was annoying.
-
Sep 23rd, 2015, 03:07 PM
#25
New Member
Re: Application.ScreenUpdating = false does not work
Originally Posted by westconn1
AFAIK the is never any need to set application.screenupdating to true, as this will happen automatically when the code finishes
This is actually not entirely accurate... If VBA code is called from outside of the environment (eg., A .NET Framework add-in), screenupdating won't reactivate, and everything will appear frozen. I always recommend setting it back to True when your code finishes. If you have routines calling other routines, then you probably don't want it to turn back on in each routine, but at the very end, I'd say re-enable it.
I've also seen cases where setting screenupdating=False doesn't appear to hold, usually, in my use cases, this is the result of Selecting or Activating other worksheets. I agree completely with westconn1 that reading and writing fully qualified ranges is a far superior practice to selecting everything, but sometimes if you're working with someone else's code, it may just make sense to leave it. In those cases, it can help to reset screenupdating back to false after the Select or Activate statement, and doing so may save a lot of time versus refactoring a giant pile of code.
Hope this helps someone...
-
Feb 18th, 2020, 03:56 PM
#26
Member
Re: Application.ScreenUpdating = false does not work
I realize this is an old thread ... however for future viewers, I found the contents very enlightening. For a complete description of my flicker problem and how I solved it (using some of the tricks outlined here in this thread/on this Forum) please refer to this link :
https://www.excelforum.com/excel-pro...ml#post5280766
-
Apr 1st, 2021, 09:42 AM
#27
New Member
Re: Application.ScreenUpdating = false does not work
Hi there to all.
I have read through all of the posts here. Very interesting.
I also had the problem.
What I found is that no matter what anyone say or do or have done, there is only one, yes one, instance where ...
Applicatrion.ScreenUpdating = False
... will not function the way it is suppose to.
{Keep in mind that all default event handling settings and so on, for the Excel application, needs to be in place}
The only one instance is when you have the VB Editor open and running the code (testing/debugging), and using the F8 (step-into) will have the Application.ScreenUpdating = False not being applied. Actually common sence, the screen will always update.
Other than this sole/single instance, as just mentioned, will the Application.ScreenUpdating = False statement always work.
I have tested this and checked, double checked, and found this to be true.
Regards
-
May 22nd, 2022, 03:22 PM
#28
New Member
Re: Application.ScreenUpdating = false does not work
I have a new Macro to apply image file compression to 150ppi for all images in a Word file, as we have an upload limit at Work on a customer system is 4MB.
It works OK but the PicturesCompress taskbar is flickering on the screen many times as the Macro goes through the loops.
The usual Application.ScreenUpdating = False at the top of the Macro, and True, at the bottom of the Macro - is not working.
I have tried all the solutions posted here and nothing has worked. Self taught regarding vba, so maybe I am not declaring enough?
The code is:
Sub MacroIC_21_05_2022()
'150ppi
Application.ScreenUpdating = False 'does not seem to work as CommandBar flickers and is visible
'SOURCE:Can't remember where I found the ExecuteMSO vba code
'No explicit Source for creating this by jam61mar@gmail.com
'Macro "C" to compress images in Word if docx file size is too big
'If Macro C is pressed in error with no file in Open Word App
If Word.Application.Documents.Count = 0 Then
Exit Sub
End If
Dim oIlS As InlineShape
'If first Inlineshape is below 150ppi and SendKeys is not selectable as greyed out
On Error GoTo Skip
'If there are images in the file (so will do nothing if pressed in error)
If Word.ActiveDocument.InlineShapes.Count > 0 Then
'Select the first image so that the "Picture Format" Ribbon Menu appears
Word.ActiveDocument.InlineShapes(1).Select
'150ppi - this is counter intuitive as it appears before the menu
VBA.SendKeys "%W{ENTER}"
'Opens the "Compress Pictures" Sub Menu on Picture Format
'A different version appears if the above Select 1st image line is switched off, so that line is critical for the actual sub menu
Application.CommandBars.ExecuteMso "PicturesCompress" '20-05-2022 Can add brackets around the speach marks
Skip:
End If
'Restarting a loop for the rest of the images in the Active Document
For Each oIlS In ActiveDocument.InlineShapes
If Word.ActiveDocument.InlineShapes.Count > 1 Then
Dim i As Integer
For i = 2 To Word.ActiveDocument.InlineShapes.Count
'If the Inlineshapes are below 150ppi and SendKeys is not selectable as greyed out
On Error GoTo SkipTwo
Word.ActiveDocument.InlineShapes(i).Select
VBA.SendKeys "%W{ENTER}"
Application.ScreenUpdating = False
Application.CommandBars.ExecuteMso "PicturesCompress"
Next i
SkipTwo:
End If
Next
End Sub
-
May 23rd, 2022, 03:46 AM
#29
Re: Application.ScreenUpdating = false does not work
i guess the question here is- do the document images update in the document when compressed during the iteration of the loop? the flickering of a command bar may not necessarily be considered a screen update
an option may be to make the document or application to visible = false while the code is running
i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next
dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part
come back and mark your original post as resolved if your problem is fixed
pete
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|