Turning off display while VBA running

Seems to me I once saw an option I can turn off to keep Excel from updating 
the display while my program inserts rows, fills in data, hides/unhides rows 
and such, so it'll run faster if there's a lot to be accomplished...the idea 
being that the program will turn the display, so to speak, back on just 
before it gets to End Sub.  Now I want to use that feature and I can't find 
it in my VBA/Excel chm.  Can anyone tell me where to look for it?  Thanks.
0
Utf
2/21/2010 1:31:01 AM
excel.programming 6508 articles. 2 followers. Follow

3 Replies
1738 Views

Similar Articles

[PageSpeed] 14

Application.Screenupdating

Tim

"Bob Bridges" <rhbridg.RemoveThisNode@attglobal.net> wrote in message 
news:370B878B-5DA0-4061-9BD3-B5539093F746@microsoft.com...
> Seems to me I once saw an option I can turn off to keep Excel from 
> updating
> the display while my program inserts rows, fills in data, hides/unhides 
> rows
> and such, so it'll run faster if there's a lot to be accomplished...the 
> idea
> being that the program will turn the display, so to speak, back on just
> before it gets to End Sub.  Now I want to use that feature and I can't 
> find
> it in my VBA/Excel chm.  Can anyone tell me where to look for it?  Thanks. 


0
Tim
2/21/2010 1:35:27 AM
Try:

Application.ScreenUpdating = False
Application.ScreenUpdating = True

At the beginning and at the end of your code.

If your code calls other code to do some "stuff" then you may still get 
screen flickering as that code excetutes, so you may need to include

Application.ScreenUpdating = False
Application.ScreenUpdating = True

in that called code also.

Also knowing that once you go to the other called code the
Application.ScreenUpdating = False is now negated in the original code and 
when the code comes back to the next line of the original code and you may 
get screen flickering again as it continues.

HTH
Regards,
Howard


"Bob Bridges" <rhbridg.RemoveThisNode@attglobal.net> wrote in message 
news:370B878B-5DA0-4061-9BD3-B5539093F746@microsoft.com...
> Seems to me I once saw an option I can turn off to keep Excel from 
> updating
> the display while my program inserts rows, fills in data, hides/unhides 
> rows
> and such, so it'll run faster if there's a lot to be accomplished...the 
> idea
> being that the program will turn the display, so to speak, back on just
> before it gets to End Sub.  Now I want to use that feature and I can't 
> find
> it in my VBA/Excel chm.  Can anyone tell me where to look for it?  Thanks. 


0
L
2/21/2010 1:45:58 AM
hi
at the begining of our code, add this......
Application.ScreenUpdating = False

then before the sub finishes, add this.......
Application.ScreenUpdating = True

if you turn screenupdating off, always be sure to turn it back on.

Regards
FSt1
"Bob Bridges" wrote:

> Seems to me I once saw an option I can turn off to keep Excel from updating 
> the display while my program inserts rows, fills in data, hides/unhides rows 
> and such, so it'll run faster if there's a lot to be accomplished...the idea 
> being that the program will turn the display, so to speak, back on just 
> before it gets to End Sub.  Now I want to use that feature and I can't find 
> it in my VBA/Excel chm.  Can anyone tell me where to look for it?  Thanks.
0
Utf
2/21/2010 1:47:01 AM
Reply:

Similar Artilces:

running total 03-15-08
i need to have a running total in a query. example date item qty needed onhand available 03/18/08 1 500 2000 1500 03/19/08 1 1500 2000 0 3/20/08 1 250 2000 -250 so the available column changes based on the available of the prior line. i want this to continue until the item changes. is this possible. thanks Use a subquery to get the sum of the previous values for the same item. You will want type an expression like this into the Field row of your qu...

Dynamics Currency Problem
Hi, Bit of a strange problem with Dynamics. We have Dynamics set to run on two Citrix servers. One for our UK users, and one for our US users. All of a sudden the smart list exports for our US users are appearing in Pound Sterling as apposed to Dollars. I assume that the currency is taken from the regional settings, is this correct? If so, then is it the local settings of the users PC or is it the profile regional settings on the citrix server? I hope someone can shed some light on this as i am lost as to why this has happened to all US users all of a sudden? Many thanks in advance ...

Excel VBA
Hi VBAers, I put some code in a workbook that I now want to re-use. My problem is I cannot remember which workbook it's in & thus, it could be in one of 30 workbooks. I was wondering if anyone can suggest a better way to find the code rather than searching for it 30 times please? Also, I think best practice is that I put my favourite code into my personal.xls file - is that correct? kazzy was thinking very hard : > Hi VBAers, > > I put some code in a workbook that I now want to re-use. My problem is > I cannot remember which workbook it's in & thus, it could be ...

Pictures don't display in tables
Have been inserting jpegs into word tables for years. Suddenly the picture doesn't display in normal or print mode. Instead I get an border. If I click inside of the border the drag handles appear but they are not active. It's a pain I can live with as the pictures do print however, I can't size them. What have I messed up? -- Joe Hi Joe, You might want to check if you have the option "Show picture placeholders" enabled? Its help text indicates "Display an empty box in place of each graphic in the document to improve scrolling performance....

Displaying on a controls
Hi, I want to display a text on a button control and static control from a my program. I tried doing SetButtonText() API , but it is not displaying it on a button control. Is their any API's to display a text on a" Button" and "Static" controls. SetWindowText joe On 28 Feb 2007 20:35:50 -0800, "lucky" <Laxmanmaruthy@gmail.com> wrote: >Hi, > >I want to display a text on a button control and static control from a >my program. >I tried doing SetButtonText() API , but it is not displaying it on a >button control. > >Is thei...

Running a select from the immediate window
I want to create and test my queries in the immediate window before adding them to my subs. In the immediate window i was able to create a table. DoCmd.RunSQL "create table test ([ff1] text (5));" I appended records using the same syntax DoCmd.RunSQL "..." Now i want to query out all the records. DoCmd.RunSQL "SELECT * from test;" Does not work. What is the correct syntax to run a select in the immediate window? thanks -- Message posted via http://www.accessmonster.com You can't. DoCmd.RunSQL only works with action queries: Ins...

VBA Code for Montecarlo Simulation
I would like to set up a spreadsheet intended for Montecarlo simulations to iterate a number of times (say 1000), while saving the frequency of each outcome on a separate table or graph. I know that pressing F9 recalculates the spreadsheet, generating a new outcome each time. What would the VBA code look like for this routine? Rafage- Save a lot of hassle by using the free add-in PopTools, which incidentally includes a superb rng (current state of the art for MS sims). Dave Braden Rafaga wrote: > I would like to set up a spreadsheet intended for Montecarlo simulations to > it...

the date value of series is incorrectly displayed
I'm drawing a 3d graph with 2 series of data. The graph is displayed as 2 separate rows of bars behind each other. In Excel 2007 the type is Colum in the first row to the far right. 1. series is a units of work. y -axis displayed in units of Thousands 2. series is a date when the units were done. x -axis. If I hover over the the date bar in the 2nd bar row I can see all the right values including the right date. the table is: 10000 1.11.2009 10000 3.11.2009 50000 10.11.2009 (in fact todate() function All the dates are displayed as 9.02.1900 if I try to dis...

Displaying images
Hi, I wanted to create an application that can display RGB images. Can anyone give me any suggestions, tips, pointers, links to tutorials/samples et al. on whats the easiest way to do this? Hope to hear from someone out there. You need to create a HBITMAP (this can be done using CBitmap), create a compatible DC (using CreateCompatibleDC), and select it into that DC. Then you can use BitBlt or StretchBlt to draw from the compatible DC to the Window DC (usually within a WM_PAINT message). "Rookie" <dominicjoseph@rediffmail.com> wrote in message news:%23qpzgJzbEHA.1004@TK2MS...

Open Files, perform action - fails
The following code casues an application defined error and I need hel understanding why. The code for finding the word "closed" in column works fine in a worksheet all by itself. And the code that reads file list from a directory works fine by itself. However, the tw together seem not to behave together...Any thoughts???? Sub OpenWorkbooksInLocation() Application.ScreenUpdating = True 'to see whats happening Dim i As Integer With Application.FileSearch 'find files .NewSearch .LookIn = "C:\Test" 'Amend to suit .SearchSubFolders = False .Filename = "...

VBA in Access or Outlook
I have a table containing email addresses and other data that I need to email out to each recipient in the table. Each email will contain in the body of the message only the data relative to the particular recipient. My question is which application Access or Outlook is the best one to code the module in to do this? I am using Access 2003 and Outlook 2003. Use the SendObject command in Access VBA. "Susan" wrote: > I have a table containing email addresses and other data that I need to email > out to each recipient in the table. Each email will contain in the body of ...

Run-time Error 7
Hi: Every time that I open Microsoft Excel 2002 (opening a blank spreadsheet), I get a Visual Basic dialog box that says "Run-Time Error 7". It does not seem to be a problem, really. I just press the "End" button in this VBA dialogue box and Excel opens just fine. It's more of a nuisance than a problem. Does anyone know how to get rid of this box so that it doesn't pop-up? Thanks! childofthe1980s Hi There's a poorly written addin residing either in Excel's startup folder (see Tools > Options for location) or installed (See Tools > Addin for...

Rules window doesn't display
When I go to Tools: Rules the rules wizard flashes and goes away. I cannot get it to display at all. This happens on 2 pc's and my laptop. Anyone have an idea of what is going on and how I can fix this? ...

Displaying value of a variable on a form
Hello, I have a bound form which has several text boxes, each box is for a date. When the form opens, the Open event (through 5 IF statements) counts & increments the number of these boxes that have null values. I have a variable for this count. I would like to display the value of this count on the form. I have a text box for this. At the end of the event, I set the value of the text box = to the variable which is keeping count. The event runs fine but on the form, the text box displays the following: #Name? Can anyone explain why this happens and how to correctly display t...

How do I get docs to display in Details mode instead of idons?
When I open a folder in MS Word, in order to see the documents, I see a bunch of icons. I want to see a Details view, which shows the name and date of each document. And I don't need to see how much space it occupies. How do I set it up to do this, instead of changing the view each time? hi Leslie, You could try setting up the view as you want it & then pressing [ctrl + cancel]. I'm not sure if it will work for you, but if not, have a read of some of the suggestions listed in this search: 'settings in file open dialogbox to show details - Google Search' (h...

Why is "Do not check spelling or grammar" always turned on
Hi everyone, this happens to me with (almost ?) every message I start in Outlook. Happens for New Messages and Replies equally. I do use a standard E- Mail Signature. Could that have something to do with it? Whenever I select the text and do Shift-F1 the "Do not check spelling or grammar" checkbox is selected when I click on Language. Why is this thing turned on? I know how to turn it off, but I don't want to have to do this for every message. Does anyone know the registry setting or option that is making this happen? Thank you. Martin Kultermann eStar Commu...

Display image from sql database
I have an asp.net page that has an image control (named ImgSignature) inside a Panel. When the panel is visible, I want to get the image from the database varbinary(MAX) column and insert it into the asp Image control. I have the following code but is not working. Can someone help? Thanks. Using conData As SqlConnection = New SqlConnection(DBClass.GetCoreConnectionString) intPeopleLinkID = Convert.ToInt32(Request.QueryString("plid")) Const SQL As String = "SELECT [MIMEType], [SignatureImage] FROM [ClientS...

Recommendations for VBA Book, manuel
This is a multi-part message in MIME format. ------=_NextPart_000_00C3_01C38E7B.CCF29F40 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable Hello all, I need a book to assist me in learning simple VBA code to use mostly = in Excel. I have a little programming experience with C++ from college, = but not a lot. My need is mostly to create forms in Excel and maybe = other Office applications in the future. I would like to teach myself = the basics of format and typical thought process in Visual Basic. I have = a solid understanding of Exce...

VBA to access data from another spreadsheet
If cell a1 contains the name of a file like : Costings and this is th name of a completely separate spreadsheet that is in C:\data, what VB code can I use to utilise the data in A1 to create a path to looku data from the costings spreadsheet -- Message posted from http://www.ExcelForum.com Dov, You don't necessarily need VBA. If you want to use VLOOKUP to look in another workbook, it might look like the following. The external workbook name (Costings.xls) is hard coded into the formula. The value being looked up is in B1, and the table in Costings is in A2:B7: =VLOOKUP(B1, [Costi...

excel vba
I have set up my sheet to protect using a password name login which the runs the macros. It is set up so that the sheet's cells cannot b edited, but the person can copy/paste the info onto another sheet. This is a problem because there are hidden cells which contai sensitive data. Any ideas -- Message posted from http://www.ExcelForum.com Hi, just a comment that i just found out, check out this link that I was refered too. That password that you setup doesn't do squat when you have access to this... http://www.straxx.com/excel/password.html I was mad when I saw this, when mean...

Want chart to display total of values
Hello, I have 7 worksheets of credit card charges that I want to graph by date. My data is in two columns in each sheet: Date Balance 1/1/1998 $432.11 etc. What I want is to have the total debt of all the cards charted along with the debt of each card individually. The problem is that the dates of the transactions varies widely. I've tried to setup a pivottable and pivotchart to display this, but I am not familiar enough with them to make it work right. Help! I want to track how well I'm paying off my debt! :) Thanks in advance, -John ...

How do i shrink a page so it is displayed twice on one side
I have deisgned a flyer in full page but would like to shrink its size so i can fit two copies on one side of paper. The way I would do it is start with a new worksheet, the size that would equal � of the paper to be printed on. Leaving Publisher opened, open the original, highlight all, copy, and paste to the new worksheet. Adjust the size of text and images as needed. -- Don Vancouver, USA "Bumps & Babes" <Bumps & Babes@discussions.microsoft.com> wrote in message news:02CD8EAF-ABAD-4C0A-BA18-2E72F58D1DB7@microsoft.com... > I have deisgned a flyer in fu...

Publisher no longer displays transparent gradients correctly.
I have been working faithfully w/Pub 2003 now for a few years; I use the program predominantly for superimposing MS drawing objects and text over imported jpgs and other photo/graphic formats, which I then export with Save as Picture. As such, I have a "template" file where I keep several designs I have created and use regularly. All of these consist of a basic MS drawing object shape grouped with Word Art. The fill (for both) consists of a gradient with separate transparency settings for each color. I have never experienced *any* problems whatsoever with Publisher until r...

Hyperlinks displayed incorrectly when composing
Hello :-) I am using Microsoft Outlook 2003 and send/reply to e-mails in HTML, using Word as my editor. I am having a problem whenever I compose, reply-to or forward an e-mail. Once the compose window opens up, whenever you create links in the body of the message (or if any are present already), they are not displayed in the usual way of being underlined like they would in MS Word. Instead they format as: { HYPERLINK "http://www.example.com/" } However when they are sent, they appear to the recipient in the usual way or being blue and underlined so you can click them. Is there a...

Cell Display Box
What is the box at the top of a spread sheet called that displays the content of the cell you are in and if it isn't displayed how can you display it? The FORMULA BAR Click on the cell required? Jonah > It is called the Formula Bar, and you can display it by going to the Tools menu, choose Option, then View. There, put a check next to "Formula bar". -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com chip@cpearson.com "Chitown" <anonymous@discussions.microsoft.com> wrote in message news:3CCB4208-022B-...