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).

 

[GTO]

... have tried the code posted by GTO. In short, it kind of worked. When opening the 'utility' workbook, it remains as the active workbook, instead of the intended previous active workbook...

If I have one or more other workbooks open, the window to the one with focus becomes active after opening the 'utility' wb. I have tested opening the utility wb thru Excel and thru Win Explorer. I am using Excel 2010, but cannot imagine this to be the issue.

Do you have ANY other code included in Workbook_Open or Autpen? If yes, please post it.

I will be logging out for the day shortly, but am confident Jaafar or another member will spot what is reactivating the utility wb.

Mark

 

[craigexcel]

Thanks Mark. No, thee is no other code included in the Workbook_Open, which is in the "This Workbook". Only what you had posted previously. There are a number of other macros within the 'utility' workbook, but all of those are in their own Modules. The only potential concern that comes to mind, and only because I'm not familiar with them at all (I copied from other posts I found for what I was trying to do), is the following. (I only included the "Dim ..." part.) Would any of these possibly cause an issue with the "Workbook_Open" process?

Dim objFSO      As FileSystemObject
Dim objFolder   As Object
Dim objFile     As Object

 

[Jaafar Tribak]

Try introducing a brief delay before running the macro and see if it works for you :

Private Sub Workbook_Open()
  If Workbooks.Count > 1 Then
     Application.OnTime Now, Me.CodeName & ".ActivateWb"
  End If
End Sub

Private Sub ActivateWb()
    ActiveWindow.ActivateNext
End Sub

 

[craigexcel]

Eureka! (Well, you found it! ) That solved the issue. THANK YOU, Jaafar! And of course, I'll ask: why did that solve the issue? Would like to learn why this fixed it, so I can handle (or avoid) it in the future.
Great Voltaire quote, too!

 

[Jaafar Tribak]

Eureka! (Well, you found it! ) That solved the issue. THANK YOU, Jaafar! And of course, I'll ask: why did that solve the issue? Would like to learn why this fixed it, so I can handle (or avoid) it in the future.
Great Voltaire quote, too!

Glad you had this working in the end

Based on my experience, sometimes, for various reasons, a vba command is not actually executed until the initial calling routine exits .. The Workbook_Open event routine is one of those routines because sometimes, the workbook takes too long to be loaded and thus ties up excel.

 

[craigexcel]

Glad you had this working in the end

Based on my experience, sometimes, for various reasons, a vba command is not actually executed until the initial calling routine exits .. The Workbook_Open event routine is one of those routines because sometimes, the workbook takes too long to be loaded and thus ties up excel.

Thanks again, Jaafar! That must (may??) be related to the fact that this is a network document, and the delay in loading could well be attributable to that.

 

[Bengt]

When you work with Winword, you can access the most recent document by adding "/mfile1" after the name of the exe. Is there a similar solution for Excel that doesn't include VBA-code?

Bengt