Screen Flicker

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

dim - 27 Nov 2007 21:30 GMT

Hi folks,

When I run a macro I've disabled some screen flicker using
ScreenUpdating=False which works fine. But part of my macro is to open then
close another workbook, and when it opens it automatically displays for a
second until it closes again. Is there any way I can stop this?

Maybe there's some sort of option in Excel that doesn't open new workbooks
as the front window and I could include the selection of this option as part
of my macro.....

Any ideas are much appreciated?
Thanks

Reply to this Message

Jon Peltier - 27 Nov 2007 21:33 GMT

If screen updating is set to false, you should not see a workbook opening
and closing. What version of Excel?

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

> Hi folks,
>
[quoted text clipped - 11 lines]
> Any ideas are much appreciated?
> Thanks

Reply to this Message

Chip Pearson - 28 Nov 2007 01:24 GMT

ScreenUpdating should do it. I'm not sure why it wouldn't. You could try the
following:

Public Declare Function LockWindowUpdate Lib "user32" (ByVal hwndLock As
Long) As Long

Sub OpenWorkbook()
Dim XLHWnd As Long
On Error GoTo ErrH:
XLHWnd = Application.Hwnd
LockWindowUpdate XLHWnd
Workbooks.Open "C:\Book1.xls"
ErrH:
''''''''''''''''''''''''''''
' Be SURE (!!) that
' LockWindowUpdate 0& gets
' called regardless of any
' error.
''''''''''''''''''''''''''''
LockWindowUpdate 0&
End Sub

Be SURE (!) to call LockWindowUpdate passing it a 0, regardless of whatever
path code execution takes or whatever errors may occur.

Signature

Cordially,
Chip Pearson
Microsoft MVP - Excel, 10 Years
Pearson Software Consulting
www.cpearson.com
(email on the web site)

> Hi folks,
>
[quoted text clipped - 11 lines]
> Any ideas are much appreciated?
> Thanks

Reply to this Message

dim - 28 Nov 2007 17:35 GMT

Jon, its Excel 2002.

Here's my code:

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 27/11/2007 by D. Murphy
'
' Keyboard Shortcut: Ctrl+a
'
ScreenUpdating = False
Workbooks.Open Filename:="C:\Program Files\test\Book2.xls"
Sheets("Sheet1").Select
Rows("2:2").Select
Selection.Insert Shift:=xlDown
Windows("Book1.xls").Activate
Sheets("Sheet2").Select
Rows("2:2").Select
Selection.Copy
Windows("Book2.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWorkbook.Save
ActiveWorkbook.Close
Sheets("Sheet1").Select
Range("H14").Select
Selection.ClearContents
Range("H12").Select
Selection.ClearContents
Range("H10").Select
Selection.ClearContents
End Sub

Thanks for that advice Chip, I'll try it out.

> ScreenUpdating should do it. I'm not sure why it wouldn't. You could try the
> following:
[quoted text clipped - 36 lines]
> > Any ideas are much appreciated?
> > Thanks

Reply to this Message

Jon Peltier - 28 Nov 2007 21:42 GMT

I don't know if it leads to your flickering, but there are a lot of
inefficiencies in your code. In general it is not necessary, and even not
desirable, to select each object before doing something with it.

This whole piece:

> ScreenUpdating = False
> Workbooks.Open Filename:="C:\Program Files\test\Book2.xls"
[quoted text clipped - 9 lines]
> SkipBlanks _
> :=False, Transpose:=False

reduces to this:

Workbooks("Book2.xls").Sheets("Sheet1").Rows("2:2").Insert Shift:=xlDown
Workbooks("Book1.xls").Sheets("Sheet2").Rows("2:2").Copy
Workbooks("Book2.xls").Sheets("Sheet1").Rows("2:2").PasteSpecial _
Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False,
Transpose:=False

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

> Jon, its Excel 2002.
>
[quoted text clipped - 78 lines]
>> > Any ideas are much appreciated?
>> > Thanks

Reply to this Message