Screen updating and formulas

With Excel and VBA, as soon as you add a formula to a cell this updates the
screen. Does anyone know a way around this?

E.g..

with Application
    .Screenupdating = False
    .range("a1").Formula ="=sum(a2:a4)"
    .range("b1").Formula ="=sum(b2:b4)"

In the example above the screen updates after adding in the formula to cell
"A1"

Any ideas from anyone would be appreciated.



0
jj933 (5)
1/4/2004 5:31:33 AM
excel 39879 articles. 2 followers. Follow

2 Replies
452 Views

Similar Articles

[PageSpeed] 42

"John" <jj@hotmail.com> wrote...
>With Excel and VBA, as soon as you add a formula to a cell this updates the
>screen. Does anyone know a way around this?
>
>E.g..
>
>with Application
>    .Screenupdating = False
>    .range("a1").Formula ="=sum(a2:a4)"
>    .range("b1").Formula ="=sum(b2:b4)"
....

Try  .Calculation = xlCalculationManual  after disabling screen updating to
turn off automatic recalculation as well, then where you re-enable screen
updating, add  .Calculation = xlCalculationAutomatic  and  .CalculateFull .

-- 
To top-post is human, to bottom-post and snip is sublime.


0
hrlngrv (1990)
1/4/2004 5:54:05 AM
I can't reproduce that behavior:

Sub Tester1()
With Application
    .ScreenUpdating = False
    .Range("a1").Formula = "=sum(a2:a4)"
    .Range("b1").Formula = "=sum(b2:b4)"
     MsgBox "Screen Updated?, No I think "
    .ScreenUpdating = True
End With
End Sub

the screen is not updated until the macro ends.

--
Regards,
Tom Ogilvy


John <jj@hotmail.com> wrote in message
news:OPWV#Ro0DHA.2408@tk2msftngp13.phx.gbl...
> With Excel and VBA, as soon as you add a formula to a cell this updates
the
> screen. Does anyone know a way around this?
>
> E.g..
>
> with Application
>     .Screenupdating = False
>     .range("a1").Formula ="=sum(a2:a4)"
>     .range("b1").Formula ="=sum(b2:b4)"
>
> In the example above the screen updates after adding in the formula to
cell
> "A1"
>
> Any ideas from anyone would be appreciated.
>
>
>


0
twogilvy (1078)
1/4/2004 4:09:20 PM
Reply:

Similar Artilces:

Security UPdate
Fellow Pubbers, Seems Microsoft is offering a free CD containing all the Security Updates for Windows 98, 98SE, ME, 2000, & XP. It can be ordered at: http://www.microsoft.com/security/protect/cd/order.asp Part II of this message: In order to avoid condemnation from the Publisher Purists, I would also appreciate the solution on how I would add the umlauts to my prose and poetry when writing in Swahili using the Icelandic version of Publisher 2000? Now if you respond to the second part of this message, we do offer therapy. -- Don ----- A Democracy: Where four wolves and one lamb vo...

Not updating objects when changing datasource
Help please :) I have created an excel report, that retrieves its data from a view. From that data i have created several sheets with different pivottables. The view is based per country, so each country has to connect to their own view. So I use the same file and change the datasource. It looks fine when i have updated. BUT when I use the dropdown on a field, the old values from the last data are still visible, however they dont show anything if you choose them. For example France has sales men a +b and Sweden c+d. when switching datasource from france to sweden, only c+d should be visible i...

Outlook Web Access
Hi Can anybody help me with some simple instructions on how to customise the login screen on Outlook Web Access...? I've found a number of "planetary" explanations, but if there are any simple ones out there..?, that would be great... Any help apprciated Colin (PS - I'm okay with Frontpage..) ...

why does my screen keep freezing up on me
every so often my screen freezes,after about 20 or 30 seconds it comes back on, anyone know how to resolve this? Do you see anything on the status bar (bottom of Excel Window) like "Saving Files" Unfortunately Excel will not do this when you are away from your PC but waits for you to do something in Excel before checking to see if it is time to save -- in other words when you are busy. Tools, Autosave (bring up the item) --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: ...

updating bond prices
WHen I update my portfolio online from my broker it does not update the bond prices correctly. Does anyone else have this problem/ I have money 2002 Most Mutual funds and bond prices are from the previous day, if that is what you mean. Stock are updated with a 20-minute delay. >-----Original Message----- >WHen I update my portfolio online from my broker it does >not update the bond prices correctly. Does anyone else >have this problem/ I have money 2002 >. > Unlike mutual funds, bonds can't be updated automatically. They don't trade on an exchange, so there...

Fill Options & Formula Help
I am trying to drag a formula across many cells from a seperate spreadsheet, however i am having a few difficulties. On the second sheet i am wanting to fill cells horizontally (a1, a2, a3 etc) with information from the first sheet (a1, b1, c1, d1 etc). For some reason i cannot do this without manually going through and changing the formula. Is there a way this could be acheived? Thank you GOto sheet2 cell a1 Type the "=" to start the formula Switch back to sheet1, select cell A1, press enter Now use your normal fill handle to replicate this formula across shee...

Updating column values only if value is not null
Hi, I have 2 tables. I have a query that updates the values of some columns from table A with values from some columns of table B based on a given condition. Something like this: Update T1 Set T1.C1 = T2.C1, T1.C2 = T2.C2, T1.C3 = T2.C3 FROM A T1 INNER JOIN B T2 ON <my condition> That's simple to do, but I was wondering how I can only update the columns where the value of the column of table B is NOT NULL. The reason is because I don't want to overwrite not null values in table A with a NULL value. In that case I want to leave as it is. Thanks Upda...

Problems after latest windows update
Hi all, Using XP, installed latest windows update yesterday and today it installed them and asked for a re-boot. Upon re-boot it asked for my windows XP password. I never had one so I shut down and re-started. This time it booted up ok. But then AVG asked me for a license number. I never had one. Also notice no sound from PC. So, re-boot again and again looked for windows XP password. Several re-boots later and I am now back in and AVG is now also working ok. Still no sound. Also, al the webistes I visit that usually ask for passwords etc are asking me to enter username, passw...

Not updating
We have received a purchase invoice for an item and it was already posted; the problem is that it did not reflect on the item inquiry transaction and it did not increase the stock. When we created an invoice for the item, it shows zero (0) in the printout but in the sales transaction window we are able to input the qty. This is very mysterious and surprising. It should have popped-out the "override shortage message" PLEASE HELP ...

How to stop screen so i can read blue screen message
Hi, I have XP pro and it has started to keep on rebooting at start up with a flash of a blue screen message which I can't read as it is so fast. It boots up with the Windows logo screen and the blue lines showing it working for a few seconds but then it goes black and then the blue screen flashes and it then reboots to the same thing again. How can I read that blue screen and how can I see what it wrong and fix it. Thanks oz "rock" <1940@pobox.com> wrote in message news:#bLvKtrpKHA.5760@TK2MSFTNGP02.phx.gbl... > Hi, > > I have XP...

FW: Check out corrective update for Microsoft Windows
--sliujiore Content-Type: multipart/related; boundary="zthgctxf"; type="multipart/alternative" --zthgctxf Content-Type: multipart/alternative; boundary="nsjtdzuhxfdunaqs" --nsjtdzuhxfdunaqs Content-Type: text/plain Content-Transfer-Encoding: quoted-printable Microsoft Client this is the latest version of security update, the "October 2003, Cumulative Patch" update which resolves all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to continue keeping yo...

CE 6.0 R2 and R3 update
I have applied all updates inclusive 2009M09 to my CE 6.0 R2 environment. Now I am in doubt if I can update this environment to 6.0 R3 as microsoft writes: "Do not install monthly update rollups released for Windows Embedded CE 6.0 R2 environment (2008 Product Update Rollup, January 2009 - September 2009 monthly rollups)" Will my environment work if I apply R3 to it or must I reinstall the whole platform builder? What they mean is that, ONCE YOU INSTALL R3, don't update it with R2 QFEs. R3 includes all of the updates that the QFEs for R2 included. P...

Setting up a continually updating average
I enter data on a daily basis. There is one column on a particular worksheet in which I need a running average -- that is, average the column, and when I enter new data, instantly update the average, so my average is always up-to-date -- automatically. I'd love to know how to set this up. A second question: How can I apply this formula or function of a continuous update average to a portion of a column, instead of the whole column? I can identify the starting cell, but the ending cell will change every time I enter data. Thank you very much. Use a defined name range. Insert>name>...

Money 2007 Update
Every time I start MS Money, I am repeatedly being asked to install an update, and then it can't install the update. Is there a fix for this for Windows XP SP3? Not sure if this is related, but also out of nowhere suddenly all of my passwords associated with MS products seemed to have been lost and won't remember my passwords. Any help would be appreciated. ...

hiding formulas
I want to be able to hide formulas from people that are using the spreadsheet. When you click on a cell that has a formula, I don't want it to be shown. Thanks Bill Select cell(s) with formula(s). Format>Cells>Protection check "hidden". Tools>Protection>Protect Sheet. Sheet must be protected to hide formulas with this method. Gord Dibben Excel MVP - XL97 SR2 & XL2002 On Fri, 11 Jul 2003 12:42:51 -0700, "Bill" <wkeasler1@carolina.rr.com> wrote: >I want to be able to hide formulas from people that are >using the spreadsheet. When y...

Screen Size
How do I get my webpage to automatically resize to fit everybodys screen? Thanks, Scott By using a percentage width in your body table rather then a fixed width. Something you can only do with a actual html editor/web design tool. Publisher has zero support for this. This is covered in the FAQ - http://www.publishermvps.com/Default.aspx?tabid=30 David Bartosik - [MSFT MVP] www.publishermvps.com www.davidbartosik.com "Scott" <scott.tuttle@delta.com> wrote in message news:0a8801c4d8ca$15508b50$a301280a@phx.gbl... > How do I get my webpage to automatically resize to ...

Taste security update that came from the MS
--psvhmtimecjafnkn Content-Type: multipart/related; boundary="mkhmmgfhzxpzyiryx"; type="multipart/alternative" --mkhmmgfhzxpzyiryx Content-Type: multipart/alternative; boundary="sotoaqhbsnskjyqr" --sotoaqhbsnskjyqr Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Customer this is the latest version of security update, the "November 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three new vulnerabilities. Install now to p...

Interest Formula #3
Does anyone know of a formula that will calculate the amount of interest that is in each instalment of a monthly repayment. Eg amount of borrowing £5000 repayment over 3 years interest (say) 7%. What is the capital element and the interest element of each instalment. Have a look at... PPMT and IPMT Hope this helps! In article <46D82352-8E60-4617-B5DF-FBA2B4B9CBE5@microsoft.com>, "Allan" <Allan@discussions.microsoft.com> wrote: > Does anyone know of a formula that will calculate the amount of interest that > is in each instalment of a monthly repayme...

Splash Screen & Login Screen
Is there any way to modify the images on the Splash Screen, or the Login Screen to make it more branded to our company? Thanks! ...

Formula appears instead of value
What would cause a formula to display instead of the result. For example in sheet1 A1 if I enter =Sheet2!A1 I will get =Sheet2!A1 displayed in the cell. I should point out this problem does not occur if I select a different range on the same sheet. Hope you can understand what my problem is! Pat Most likely the cell is formatted as Text. Change the format to General, and press F2 then Enter. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Pat" <glass_patrick@hotmail.com> wrote in message news:ejkP1YMBFHA.1452@TK2...

Touch Screen Department & Categories
I have a need to setup Touch Screen in RMS. But I need to be able select a Department and then have an additional prompt of a catagory. Right now the way RMS works is you can only have RMS setup so you can see all Departments but then that gives you a long list of every item in that department. We want to break it down into further sub-groups. I know that RMS doesn't do this out of the box but if any one has any ideas on how to accomplish that woule be great. David We have an RR Touch module that will do this for you and more. Contact me if you wish to try this. Afshin Ali...

Windows 7 upgrade Blue Screens my laptop. Maybe because of ATI?
Laptop's a Gateway NV52 with Vista Home Premium 64-bit. I'm upgrading to Windows 7 Home Premium 64-bit. I ran the Upgrade Advisor and it told me to uninstall ATI Catalyst Install Manager, which I did. I put in the upgrade disk, and after Phase 1 (3 total phases), it had to restart to begin Phase 2. After the Gateway logo screen, it blue screened on me. The error code was 0x7b. It didn't even do the whole mini dump thing it usually does when it blue screens. It never even makes it to the Welcome scene (where the green bar is loading on the black screen, not the Vista wel...

Excel 2002 Changes Screen Resolution
When Excel 2002 launches, it changes the screen resolution to 640x480. How do I get it to stop this? That's new one! Just to be sure that it is Excel, and not something that is loading with Excel, from Start->Run enter "Excel /s" (no quotes) and press Enter. This is "safe mode" where nothing extra runs. If it doesn't happen then you know it's an add-in or something auto-loading from ,say, XLSTART. -- Jim Rech Excel MVP "Sheila B" <anonymous@discussions.microsoft.com> wrote in message news:5c7401c42d7e$a9db5770$a001280a@phx.gbl... ...

match formulas
I have 2 spread sheets. I would like to find all matches from both sheets and put in a new spreadsheet. I've tried help files and cannot understand it. for example, the source file has first name and last name as well as other information. The data list I want to match only has first name and last name. SO after it finds the match, I would like the subsequent data from that match to be places in a new sheet. any help would be greatly appreciated ...

add text to end of formula output
I have a basic formula that I want to display a character after the calculation. example: A1+A2+A3=25.00 I would like to see: A1+A2+A3=25.00hrs Is this possible? You can use a custom format 0.00 "hours" that way you can still do calculations with the cell or =SUM(A1:A3)&" hours" which would be text -- Regards, Peo Sjoblom (No private emails please) "mgdarkman" <mgdarkman@discussions.microsoft.com> wrote in message news:884334AB-8B38-42AB-8D5A-166E6E4A12FE@microsoft.com... >I have a basic formula that I want to display a charact...