Screen Flicker

[dim]

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

 

[Jon Peltier]

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

- Jon

 

[Chip Pearson]

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.


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

 

[dim]

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.

 

[Jon Peltier]

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"
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

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