MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Thanks Celia,have 2 other ?'s.

Posted by Ted on May 04, 2000 10:43 AM

That reloacte code worked great.
1st question,when using Application.ScreenUpdate=False
Is there away to have the sheet update with out having
to put Application.ScreenUpdate=True & then back to False so the
macro runs faster.
The reason is I want the sheet to update each time the code reloactes
the charts(I can have 20 or more charts on each sheet).I
saw some where about Application.ScreenUpdate=x1manual or
something like that.2nd question,can a macro run on "computer 1"
and do things to a spreadsheet on "computer2" (on a network)and if so
do you have any idea about that part of the code.

Posted by Celia on May 04, 2000 5:12 PM


Don’t know of a way to update other than with Application.ScreenUpdating=True.
Screen updating can only be set to True or False. Calculation can be set to either xlManual or xlAutomatic.
Why do you need to update before the macro has finished? (e.g. to use an input box?). If it is really necessary to update the sheet and if you can put up with the flashing screen, why not just leave the updating on - unless your macro is very long and the there are many changes to the range selection, it is unlikely that turning off screen updating will make more than a few seconds difference to the run time. You can check the difference in the two run times by adding the following code :
Dim startTime as Date
StartTime = Now()
‘Put your code here
MsgBox Format (Now() - startTime, “hh:mm:ss”)

Regarding your second question, I don’t know much about network set-ups but I suppose it can be done – probably just a question of specifying the correct path.
Have you tried to do it by using the macro recorder?