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

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
_______


"dim" wrote in message
...
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

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" wrote in message
...
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, 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.

"Chip Pearson" wrote:

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" wrote in message
...
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

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
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______


"dim" wrote in message
...
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.

"Chip Pearson" wrote:

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" wrote in message
...
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