How do i best deal with Application.ScreenUpdating = False when using lots of macros?

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,575
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi everyone,

A problem i've come across a lot recently.

Lets say I have 10 macros in my document.
each one runs and is used on its own so at the beggining i have

"Application.ScreenUpdating = False"
then code then

"Application.ScreenUpdating = True"

all works great, but then lets say I have a macro that calls on 5 of those macros

it would be something like this

Code:
Sub AllMacs
[LEFT][COLOR=#222222][FONT=Verdana]Application.ScreenUpdating = False
call mac1[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]call mac2[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]call mac3[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]call mac4[/FONT][/COLOR]
[COLOR=#222222][FONT=Verdana]call mac5
[LEFT][COLOR=#222222][FONT=Verdana][LEFT][COLOR=#222222][FONT=Verdana]Application.ScreenUpdating = true
end sub[/FONT][/COLOR][/LEFT][/FONT][/COLOR][/LEFT][B][I][U][SUB][SUP]<strike>
</strike>[/SUP][/SUB][/U][/I][/B][/FONT][/COLOR][/LEFT]
(just an example!)

so this is what i'm having a problem with

Allmacs runs, turns off screen updating, runs mac1, when mac1 end it turns screenupdating back on and my screen flickersor changes to the new frame then it switches of again and so on for all the macros.

so is there a way I can say "Until this macro is finished, ignore any commands for
Application.ScreenUpdating = true??

any ideas, if i'm doing this wrong just let me know please.

thanks

Tony
<strike>
</strike>
 

Some videos you may like

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,649
Office Version
  1. 365
Platform
  1. Windows
Easiest option is to remove all the
Code:
[COLOR=#222222][FONT=Verdana][COLOR=#222222][FONT=Verdana]Application.ScreenUpdating = true[/FONT][/COLOR][/FONT][/COLOR]
Screen updating will resume when the macros end without have to turn it back on
 

offthelip

Well-known Member
Joined
Dec 23, 2017
Messages
1,471
Office Version
  1. 2010
Platform
  1. Windows
the way i would do it so that you can call each sub separately is using the following.
Code:
Public SUflag

Sub AllMacs()
SUflag = False
Application.ScreenUpdating = False
Call Mac1
Call mac2
Call mac3
Call mac4
Call mac5
SUflag = True
Application.ScreenUpdating = True
End Sub


Sub Mac1()
Application.ScreenUpdating = False
' your code
Application.ScreenUpdating = SUflag


End Sub

You must initialise the SUFLAG to True on workbook open
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,575
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Fluff,
I like the idea but struggle to belive it?
why does everyone switch it back on if it auto restarts?
anyway i'm sure you know better so i'll give it a go.
thanks
Tony
 

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
212
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Screen updating will resume when the macros end without have to turn it back on

Hello Fluff

Please correct me if I'm wrong because I'm fairly new at Excel, but it seems to me I remember reading quite some time ago, that these types of commands stayed with Excel, even after the program ended. If they were not reset by code, they would also affect the next program that was loaded. Or am I thinking of something else?

TotallyConfused (yes I still am) :)
 

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,575
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Hi Everyone,
So I could be wrong but after testing I could not get screen update to auto reset, however Offthrlip's idea does exactly what i need. not taking away from fluffs idea, just saying in my case the other idea worked better.

Thanks

Tony
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
35,523
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Screenupdating will automatically reset to True when all your code is finished, as Fluff said. If you insist on resetting it in each routine, it will make the routines more self contained if you use a local variable in each one:

Code:
Sub anyMacro()
dim bScreen as boolean
bscreen = application.screenupdating
Application.ScreenUpdating = False
' your code
Application.ScreenUpdating = bscreen
end sub
 

Watch MrExcel Video

Forum statistics

Threads
1,109,501
Messages
5,529,234
Members
409,857
Latest member
KailuaTown
Top