open last active workbook

[craigexcel]

I have created a 'utility' workbook that contains a number of macros that coworkers & I use for various processes. Since each of the processes to be completed is always done on a workbook that a user opens, I'd like to automate always returning to the previously 'active' workbook. I've been searching for a while for a solution but have yet to come up with one.

I tried both of these in the "This Workbook" module's Autpen event, without success, and in fact, it didn't look like anything happened. (1) Application.SendKeys ("%{TAB}"), and (2) some version of "... .ActivatePrevious" (but I already deleted that one, instead of just commenting it out, since it didn't work, and I don't remember what the rest of it was).

So, my question is: does VBA code exist that will, once the 'utility' workbook has been opened, the user is immediately returned to the (still open) workbook that was just the 'active' workbook?

I'm using Office 365 (i.e. Excel 2016).

 

[Jaafar Tribak]

Put this code in the ThisWorkbook Module of the 'utility' workbook :

Option Explicit

Private WithEvents app As Application
Private oPrevWb As Workbook

Private Sub Workbook_Open()
    Set app = Application
End Sub

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    If Wb.Name = "[COLOR=#ff0000][B]Enter here the name of the workbook the user opens.[/B][/COLOR]" Then oPrevWb.Activate
End Sub

Private Sub app_WorkbookActivate(ByVal Wb As Workbook)
    Set oPrevWb = Wb
End Sub

 

[craigexcel]

Thanks Jaafar for your reply. The text you wrote, that's shaded red, would not be for a specific workbook though. The user could open any 1 of many workbooks that the macros in the 'utility' workbook can be used on. That's why I wanted to activate the 'previous' (still open) workbook, regardless of its name. Apologies if I didn't make that clear. Any other thoughts on how to accomplish this with this added information?

 

[Jaafar Tribak]

Thanks Jaafar for your reply. The text you wrote, that's shaded red, would not be for a specific workbook though. The user could open any 1 of many workbooks that the macros in the 'utility' workbook can be used on. That's why I wanted to activate the 'previous' (still open) workbook, regardless of its name. Apologies if I didn't make that clear. Any other thoughts on how to accomplish this with this added information?

Just remove that If clause line but it will activate the previous active workbook every time a workbook is opened during the session regardless of the workbook that was just opened:

Option Explicit

Private WithEvents app As Application
Private oPrevWb As Workbook

Private Sub Workbook_Open()
    Set app = Application
End Sub

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    oPrevWb.Activate
End Sub

Private Sub app_WorkbookActivate(ByVal Wb As Workbook)
    Set oPrevWb = Wb
End Sub

 

[craigexcel]

Thanks again Jaafar. I tried your revised code, but it gets stuck on the "oPrevWb.Activate" line. I'm missing something, since I can't see what "oPrevWb" has been defined as. Can you help?

 

[Jaafar Tribak]

Thanks again Jaafar. I tried your revised code, but it gets stuck on the "oPrevWb.Activate" line. I'm missing something, since I can't see what "oPrevWb" has been defined as. Can you help?

Yes, you are right .. I forgot to set the oPrevWb var when opening the 'utility' workbook !

Try this :

Option Explicit

Private WithEvents app As Application
Private oPrevWb As Workbook

Private Sub Workbook_Open()
    Set app = Application
    Set oPrevWb = Me
End Sub

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    oPrevWb.Activate
End Sub

Private Sub app_WorkbookActivate(ByVal Wb As Workbook)
    Set oPrevWb = Wb
End Sub

 

[craigexcel]

I just tried the revised code, and still no luck. The 'utility' workbook remained the active workbook after it opened, and the code didn't switch to the previous (still open) workbook, which had been the active workbook. Any other ideas? Also, as a side question, what does "Me" mean in the context of the line "Set oPrevWb = Me"?

 

[Jaafar Tribak]

Ok - Sorry I misunderstood you . So you want to activate the previous active workbook when the 'utility' workbook is opened - Right ?
If so, you may need to place the code in an addin or the personal workbook

 

[craigexcel]

Looking at your code again, I'm wondering about the "Set oPrevWb = Me" line. Since that's in the Workbook_Open section, wouldn't that set "oPrevWb" as the 'utility' workbook, since that's the workbook the code is running from?

Private Sub Workbook_Open()
    Set app = Application
    [COLOR=#ff0000]Set oPrevWb = Me[/COLOR]
End Sub

 

[Jaafar Tribak]

Looking at your code again, I'm wondering about the "Set oPrevWb = Me" line. Since that's in the Workbook_Open section, wouldn't that set "oPrevWb" as the 'utility' workbook, since that's the workbook the code is running from?

Private Sub Workbook_Open()
    Set app = Application
    [COLOR=#ff0000]Set oPrevWb = Me[/COLOR]
End Sub

Yes that's correct but I got the logic wrong because I had misunderstood your question ... I'll post the correct code, hopefully, in a moment but you will have to place the code in an Addin