J
Jason Morse
I've finally come up with a workaround for this, but decided to post
anyhow if this is useful to others.
To begin with, I am in the process of updating some macros developed
in Excel 98 so that they will work in Excel X (v10.1.5). Several of
the macros contain similar steps: (1) define an input workbook, (2)
create an output workbook, (3) process some input data and show the
output in the frontmost workbook. Easy, right?
I kept running into the following problem in Excel X: the macro
finishes and the output workbook is not the frontmost window. Odder
still, I couldn't even activate the output workbook using the Window
menu. I had to drag other windows out of the way and actually click
on the output workbook in order to activate it. Not something I could
expect my users to do.
After many unsuccessful attempts to explicitly set the active window
in the code, I finally hit upon the root of the problem --
Application.ScreenUpdating = False. If I just move this line until
*after* the output workbook has been created, the problems described
above go away.
Sub test_new_wb()
Application.ScreenUpdating = False
If Initialize_Output(ActiveWorkbook) = False Then
MsgBox "Error!!!"
Else
'Do some data manipulation...
MsgBox "OK, here's your output..."
End If
Application.ScreenUpdating = True
End Sub
Private Function Initialize_Output(wb) As Boolean
Dim wsOutput As Worksheet
Dim iInitDefaultValue As Integer
iInitDefaultValue = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set wsOutput = Application.Workbooks.Add.ActiveSheet
Application.SheetsInNewWorkbook = iInitDefaultValue
wsOutput.Name = "Output"
Initialize_Output = True
End Function
anyhow if this is useful to others.
To begin with, I am in the process of updating some macros developed
in Excel 98 so that they will work in Excel X (v10.1.5). Several of
the macros contain similar steps: (1) define an input workbook, (2)
create an output workbook, (3) process some input data and show the
output in the frontmost workbook. Easy, right?
I kept running into the following problem in Excel X: the macro
finishes and the output workbook is not the frontmost window. Odder
still, I couldn't even activate the output workbook using the Window
menu. I had to drag other windows out of the way and actually click
on the output workbook in order to activate it. Not something I could
expect my users to do.
After many unsuccessful attempts to explicitly set the active window
in the code, I finally hit upon the root of the problem --
Application.ScreenUpdating = False. If I just move this line until
*after* the output workbook has been created, the problems described
above go away.
Sub test_new_wb()
Application.ScreenUpdating = False
If Initialize_Output(ActiveWorkbook) = False Then
MsgBox "Error!!!"
Else
'Do some data manipulation...
MsgBox "OK, here's your output..."
End If
Application.ScreenUpdating = True
End Sub
Private Function Initialize_Output(wb) As Boolean
Dim wsOutput As Worksheet
Dim iInitDefaultValue As Integer
iInitDefaultValue = Application.SheetsInNewWorkbook
Application.SheetsInNewWorkbook = 1
Set wsOutput = Application.Workbooks.Add.ActiveSheet
Application.SheetsInNewWorkbook = iInitDefaultValue
wsOutput.Name = "Output"
Initialize_Output = True
End Function