Chip, your solution is intriguing -- could you please explain what the
functional difference (to end-user) is btw this solution and disabling
screen-updating?
The primary difference is who is doing the blocking of the window
updates. With Application.ScreenUpdating, Excel won't update the
screen, assuming it obeys its own rules. The setting is internal to
Excel, and for all I know, there are methods within Excel that ignore
this setting. LockWindowUpdate operates at the much lower level of the
operating system, and causes it to temporarily ignore (until the
window is unlocked) any update messages sent to the locked window.
Application.ScreenUpdating applies only to Excel and is automatically
turned off when the chain of execution in VBA terminates and is
control is returned to Excel. LockWindowUpdate can lock any window,
though only one window may be locked at any one time, and the window
lock will remain in effect until it is explicitly released.
I use LockWindowUpdate to prevent screen flickering when running code
under the VBA Extensibility model to modify VBE objects. Since the VBA
editor doesn't respect the setting of Application.ScreenUpdating (as
it should -- ScreenUpdating is part of Excel, not the general VBA
language), I use LockWindowUpdate to suppress visual changes in the
VBE. See the "Eliminating Screen Flicker During VBProject Code"
section in the middle of my "Programming The VBA Editor" page at
http://www.cpearson.com/Excel/vbe.aspx .
In nearly all cases, the end user will not notice any difference
between Application.ScreenUpdating and LockWindowUpdate.
Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
On Fri, 28 Aug 2009 07:37:49 -0700 (PDT), Ray
wrote:
Thanks for all of the responses ... it's very much appreciated.
I implemented Sam's solution first, as it was the easiest ... and
worked perfectly.
Chip, your solution is intriguing -- could you please explain what the
functional difference (to end-user) is btw this solution and disabling
screen-updating?