Turning off the gui update during vba run

plawton · Apr 24th, 2002, 08:17 AM

Is there a way to turn off the update of the user interface while running a vba program? Specifically, I am trying to import a series of *.csv files and copy them into the same workbook together. It runs slow and looks bad because you see every action as it goes through the opening and copying procedure. Any ideas would be helpful. Thanks.

Iain Lewis · Apr 24th, 2002, 08:19 AM

First line of your macro should be :

Application.ScreenUpdating = False

But you must turn it back on at the end :

Application.ScreenUpdating = True

nancyo · Apr 24th, 2002, 08:42 AM

I have used this many times, and found it not to be necessary to add the statement at the end of the macro, seems to work fine. Is this a problem waiting to happen?

Iain Lewis · Apr 24th, 2002, 08:48 AM

Not sure. I've also left it off accidently & not had any problems, but as I've been told quite a few times THAT YOU MUST TURN IT BACK ON I didn't want plawton to use it & then find a problem.
I guess it's just "good practice" to turn things back on after you've turned them off (like calculation ).

Mark O'Brien · Apr 24th, 2002, 09:22 AM

I seldom reset screenupdating back to True.

You have to know when to do it. Basically, if control gets returned to Excel at the end of the macro, i.e. the code stops running, then there's no need to turn screenupdating back on because Excel will return to default. (i.e. TRUE)

However, you can get screwed quite easily and this is the only example I have of this.

Create a userform, doesn't matter what it's called and use this code to show it:





Application.ScreenUpdating = False

Userform1.Show 'Or whatever your form is called

Start moving the form around and you'll be "painting" it all over the screen. This is not necesarily a problem, so long as you know what userform is yours. (ok it is a problem because it's a bug that we've created). This feature is particularly fun when you use "modeless" userforms and use a form to display another.

Anyway, I hope this helps. (if you're evil, like me, it's also a laugh to alter someone's code to do this, just to see how long it takes them to fix it)

Iain Lewis · Apr 24th, 2002, 09:37 AM

I like the cut of your job !

Iain Lewis · Apr 24th, 2002, 09:37 AM

Damn, that would have been better as Jib

Mark O'Brien · Apr 25th, 2002, 09:51 AM

Yup, that's what the "edit" button's for though.

Apr 24th, 2002, 09:22 AM	#5
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	I seldom reset screenupdating back to True. You have to know when to do it. Basically, if control gets returned to Excel at the end of the macro, i.e. the code stops running, then there's no need to turn screenupdating back on because Excel will return to default. (i.e. TRUE) However, you can get screwed quite easily and this is the only example I have of this. Create a userform, doesn't matter what it's called and use this code to show it: Application.ScreenUpdating = False Userform1.Show 'Or whatever your form is called Start moving the form around and you'll be "painting" it all over the screen. This is not necesarily a problem, so long as you know what userform is yours. (ok it is a problem because it's a bug that we've created). This feature is particularly fun when you use "modeless" userforms and use a form to display another. Anyway, I hope this helps. (if you're evil, like me, it's also a laugh to alter someone's code to do this, just to see how long it takes them to fix it)

Apr 25th, 2002, 09:51 AM	#8
Mark O'Brien MrExcel MVP Join Date: Feb 2002 Location: Columbus, OH, USA Posts: 3,519	Yup, that's what the "edit" button's for though. __________________ Mark O'Brien Columbus Ohio Celtic Supporters Club

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 24th, 2002, 08:17 AM	#1
plawton New Member Join Date: Apr 2002 Posts: 1	Is there a way to turn off the update of the user interface while running a vba program? Specifically, I am trying to import a series of *.csv files and copy them into the same workbook together. It runs slow and looks bad because you see every action as it goes through the opening and copying procedure. Any ideas would be helpful. Thanks.

Apr 24th, 2002, 08:19 AM	#2
Iain Lewis Board Regular Join Date: Apr 2002 Location: Midlands, UK Posts: 217	First line of your macro should be : Application.ScreenUpdating = False But you must turn it back on at the end : Application.ScreenUpdating = True

Apr 24th, 2002, 08:42 AM	#3
nancyo Board Regular Join Date: Mar 2002 Location: Massachusetts, USA Posts: 255	I have used this many times, and found it not to be necessary to add the statement at the end of the macro, seems to work fine. Is this a problem waiting to happen?

Apr 24th, 2002, 08:48 AM	#4
Iain Lewis Board Regular Join Date: Apr 2002 Location: Midlands, UK Posts: 217	Not sure. I've also left it off accidently & not had any problems, but as I've been told quite a few times THAT YOU MUST TURN IT BACK ON I didn't want plawton to use it & then find a problem. I guess it's just "good practice" to turn things back on after you've turned them off (like calculation ).

Apr 24th, 2002, 09:37 AM	#6
Iain Lewis Board Regular Join Date: Apr 2002 Location: Midlands, UK Posts: 217	I like the cut of your job !