PDA

View Full Version : Returning focus to previous workbook after opening another workbook



Longhair4277
11-11-2008, 10:39 AM
Thank you for reading this, my first post here.

This is the code (at bottom of the message) I have, with the "IsXLBookOpen" code borrowed from an article I read here yesterday. The following is code I have in a workbook (lets call it "MASTER") that needs the workbook mentioned ( lets call it "P&S") in it opened to look up data in it. So what happens now, I open MASTER and the file P&S is automatically opened, but the "focus" stays with the newly opened P&S. I realize I could use

Windows("MASTER.xls").Activate

but I copy and rename this file quite often so I do not want to have to edit the code each time. Is there a way to make this "generic"? meaing, that when I open up MASTER (after it has say been copied and renamed for the umpteenth time to say MASTER333) that it will automatically open the P&S workbook and then have focus go back to the workbook that called for the file to be opened.


Private Sub Workbook_Open()

If IsXLBookOpen("-=Products&Suppliers 2008=-.xls") = False Then Workbooks.Open "C:\NORCAP\-=Products&Suppliers 2008=-.xls"

End Sub

georgiboy
11-11-2008, 10:53 AM
When you open the new workbook using code use this refering to the newly opened workbook

Windows("2.xls").ActivatePrevious
Hope this helps

Longhair4277
11-11-2008, 11:25 AM
When I placed this code in the workbook doing hte calling, I got a subscript out of range error. When I placed it in the workbook being opened:

Private Sub Workbook_Open()
Windows("-=Products&Suppliers 2008=-.xls").ActivatePrevious
End Sub


it worked. Just want to make sure that I have it in the correct spot. Thanks for the quick reply.

georgiboy
11-11-2008, 12:09 PM
When i tried it i had a workbook on my desktop called "1" with the code in, i also had a workbook called "2". I had the code
Windows("2.xls").ActivatePrevious
in a module in workbook "1" just after the part where i opened workbook "2". If that makes sence:dunno