You can achieve this by using the Screen Updating property of the Application object.
To demonstrate the principle of this technique we'll need a small example procedure that makes lots of visible changes to the Excel workbook.
You can either download the example here, or create a new blank workbook, add around five worksheets to it and then copy the code shown below into a new module.
' This includes both posting free demo projects made from this ' code as well as reproducing the code in text or html format.
The way out of this frozen state is simple: Go back to the VBE, and execute the following statement in the Immediate window: If you have a worksheet with many complex formulas, you may find that you can speed things considerably by setting the calculation mode to manual while your macro is executing.
When the macro finishes, set the calculation mode back to automatic.
This means that if you run a different subroutine after the one above and you haven't added the line of code to disable screen updates to it, you'll be able to see the screen updating in the background.
Excel & Word have the Screen Updating method thru which a developer can lock the main window from unnecessarily redrawing itself whilst the macro is being executed. If left alone, redrawing is not only ugly on the eyes it also takes more time for the macro to reach completion.