minimize workbook

[JohnZ]

In an Open event subroutine for "workbook1" I open a second workbook
"workbook2" that is defined by the label "book2" in "workbook1" with the
following statements:

Dim wkbook2 as object
Set wkbook2 = Range("book2")
Workbooks.Open (wkbook2)

Question: How do I minimize the window for "workbook2" just opened using the
variable wkbook2? Following statement does not work.

Workbooks(wkbook2).ActiveWindow.WindowState = xlMinimized

 

[Vergel Adriano]

JohnZ,

Try something like this:


Sub test()
Dim wb2 As Workbook
Dim strFileName As String

strFileName = Range("book2")

On Error Resume Next
Set wb2 = Workbooks.Open(strFileName)
On Error GoTo 0

If Not wb2 Is Nothing Then
Application.Windows(wb2.Name).WindowState = xlMinimized
Else
MsgBox "Failed to open file:" & strFileName
End If

End Sub

 

[JohnZ]

Vergel,

Thank you for your help. Your suggested code worked great and did exactly
what I wanted to do.

I now have a couple more questions ... Following the "End If" in your code,
I added the statement:
ActiveWindow.WindowState = xlMaximized
On exit from the Open event subroutine, "Workbook2" is minimized and
"Workbook1" is the active window, and it is maximized. From my test results,
I conclude that when you open a new workbook from an Excel VB subroutine, the
new workbook becomes the active workbook. If you minimize this workbook
window, it becomes inactive and the window you are executing in becomes the
active window. Is my understnding correct?

A second question, if at a later time, via a subroutine call, I want to
minimize the "workbook1" window and activate and maximize the "workbook2"
window, what code is required to do this? (Note, from my initial question,
label "book1" in "workbook1" defines the string name of "workbook1" and
"book2" the string name of "workbook2".) Thanks again for your help.

 

[Vergel Adriano]

John,

I believe Excel keeps track of the order of all open workbooks. When you
minimize a workbook, the one that was previously active would become active,
not necessarily the one executing the code. For example, if you had workbook
A open workbooks B and C. Then, minimize workbook C, workbook B would become
active.

One way to work with workbook2 at a later time in your code is to keep a
workbook variable as a module wide variable. This way you would be able to
minimize it by something like this:

Application.Windows(wb.Name).WindowState = xlMinimized


One other way is to loop through all the open workbooks and compare if the
Fullname property matches the value of the "book2" range. Like this:

Sub minimize_wb2()

Dim wb As Workbook

For Each wb In Workbooks
If wb.FullName = Range("book2") Then
Application.Windows(wb.Name).WindowState = xlMinimized
Exit For
End If
Next

End Sub

 

[JohnZ]

Vergel,
Sorry it's taken a while for me to get back to you. Just wanted to say I
have implemented your suggested "loop through" code minimize "book2". Only
change was to use xlMaximized vs. xlMinimized. This code does the job and
works well. Thanks for your excellent help.