Can someone tell me how to write a macro that will freeze the frame
until finished. I have a file with 25 sheets which, when the file
opens, all the sheets are taken through a formatting routine. This is
fine, but you get a bit dizzy watching it. So I want to freeze the
display and show a box filling as the macro progresses. Can anyone
help?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

I minimized the workbook. You don't have to watch and the program is faster by
powers!

jb

Hi Stuart,

Put Application.ScreenUpdating=False at the beginning and
Application.ScreenUpdating=True at the end.

--
Regards,

Jake Marx
www.longhead.com


Stuart Farr wrote:
Can someone tell me how to write a macro that will freeze the frame
until finished. I have a file with 25 sheets which, when the file
opens, all the sheets are taken through a formatting routine. This is
fine, but you get a bit dizzy watching it. So I want to freeze the
display and show a box filling as the macro progresses. Can anyone
help?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

JB

This file goes to a 3rd party for their use so I need the control to
remain in the file - if I minimised the file, the 3rd party would panic
hence my need for the display to be frozen albeit temporarily. So the
question still remains



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Jake,

That's great - thanks. Any ideas on how I could demonstrate to the user
that there is a macro working e.g. grey box with blue fill in as the
macro progresses?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/

Hi Stuart,

You could use a UserForm with a ProgressBar control on it. Or you could
simply use the StatusBar in Excel (gray bar at bottom of application
window). To do that:

Application.StatusBar = "Working - please wait...."

'/ do your stuff

Application.StatusBar = False

You could, of course, put the % complete by changing the statusbar in your
loop.

--
Regards,

Jake Marx
www.longhead.com


Stuart Farr wrote:
Jake,

That's great - thanks. Any ideas on how I could demonstrate to the
user that there is a macro working e.g. grey box with blue fill in as
the macro progresses?



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from
http://www.ExcelForum.com/

Jake,

You're a star - many thanks

Stuart



------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~ View and post usenet messages directly from http://www.ExcelForum.com/