Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
minimize workbook
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
minimize workbook
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 -- Hope that helps. Vergel Adriano "JohnZ" wrote: 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
minimize workbook
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" wrote: 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 -- Hope that helps. Vergel Adriano "JohnZ" wrote: 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
minimize workbook
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 -- Hope that helps. Vergel Adriano "JohnZ" wrote: 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" wrote: 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 -- Hope that helps. Vergel Adriano "JohnZ" wrote: 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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
minimize workbook
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. "Vergel Adriano" wrote: 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 -- Hope that helps. Vergel Adriano "JohnZ" wrote: 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" wrote: 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 -- Hope that helps. Vergel Adriano "JohnZ" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Moved workbook-File name, minimize, maximize and close are not vis | Excel Worksheet Functions | |||
How do I minimize | Excel Discussion (Misc queries) | |||
Buttons for Minimize, Restore and Close missing from Workbook | Excel Discussion (Misc queries) | |||
minimize just opened workbook window | Excel Programming | |||
how do i minimize/maximize a workbook from vba? I want to minimize it durring processing to speed things up a bit | Excel Worksheet Functions |