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]

Addin code (in the ThisWorkbook Module of the AddIn):

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 InStr(UCase(Wb.Name), UCase("utility")) Then
        On Error Resume Next
            oPrevWb.Activate
        On Error GoTo 0
    End If
End Sub

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

 

[craigexcel]

Understood, Jaafar, and thanks. I look forward to the revised code, and that will give me a reason to learn about AddIns, since I haven't dealt with them before. If you can suggest an online resource you're aware of that would help get me started, that would be great. Meanwhile, I'll look at my copy of John Walkenbach's Power Programming book that I have for some resource material.

 

[Jaafar Tribak]

You can take a look here for example

I am trying to come up with an alternative workaround that doesn't need an addin (unless someone comes up with something better) ... hopefully, I'll post it here later ... so stay tuned

 

[GTO]

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

Greetings All,

I may be misreading the goal, but if it is simply to activate the last active workbook (prior to the 'utility' wb being opened), then would this work?

In ThisWorkbook module of the 'utility' workbook:

Option Explicit
  
Private Sub Workbook_Open()
  
  If Workbooks.Count > 1 Then
    ActiveWindow.ActivateNext
  End If
  
End Sub

Mark

 

[Jaafar Tribak]

Greetings All,

I may be misreading the goal, but if it is simply to activate the last active workbook (prior to the 'utility' wb being opened), then would this work?

In ThisWorkbook module of the 'utility' workbook:

Option Explicit
  
Private Sub Workbook_Open()
  
  If Workbooks.Count > 1 Then
    ActiveWindow.ActivateNext
  End If
  
End Sub

Mark

lol I had this feeling there had to be something as simple as ActivateNext to solve this .. I just completly forgot about that Method ..

Thanks for that Mark ... and sorry craigexcel for wasting your time .

 

[Jaafar Tribak]

Just so that I don't feel I have wasted my time , here is the API alternative that I enthusiastically came up with to re-invent the wheel

Option Explicit

[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=If]#If[/URL]  VBA7 Then
    Private Declare PtrSafe Function GetNextWindow Lib "user32" Alias "GetWindow" (ByVal hwnd As LongPtr, ByVal wFlag As Long) As LongPtr
    Private Declare PtrSafe Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hwnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
    Private Declare PtrSafe Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As LongPtr) As Long
    Private hwnd As LongPtr
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=Else]#Else[/URL] 
    Private Declare Function GetNextWindow Lib "user32" Alias "GetWindow" (ByVal hwnd As Long, ByVal wFlag As Long) As Long
    Private Declare Function FindWindowEx Lib "user32" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hwnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
    Private Declare Function PostMessage Lib "user32" Alias "PostMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long
    Private hwnd As Long
[URL=https://www.mrexcel.com/forum/usertag.php?do=list&action=hash&hash=End]#End[/URL]  If


Private Const GW_CHILD = 5
Private Const GW_HWNDNEXT = 2
Private Const WM_NCLBUTTONDOWN = &HA1
Private Const HTCAPTION = 2


Private Sub Workbook_Open()
    hwnd = FindWindowEx(Application.hwnd, 0, "XLDESK", vbNullString)
    hwnd = GetNextWindow(hwnd, GW_CHILD)
    If hwnd Then
        PostMessage GetNextWindow(hwnd, GW_HWNDNEXT), WM_NCLBUTTONDOWN, HTCAPTION, 0
    End If
End Sub

 

[GTO]

You are most welcome and no worries, it didn't occur to me until trying a couple other ways... on top of which I tried activatePrevious a good half-dozen times before re-reading the help topic...

 

[craigexcel]

(continued) Thanks to you both for adding your additional insight on the question. Will try / look into both to see if either works for me, as well as to understand the code Jaafar provided.
GTO -- as to your code, the thing that immediately jumped out at me, is what if the user has more than 1 workbook opened prior to opening the 'utility' workbook. Does "...ActivateNext" navigate to the previous active workbook. I'm not sure how Excel manages the queue of active / open workbooks. Thanks!

 

[GTO]

(continued) ...what if the user has more than 1 workbook opened prior to opening the 'utility' workbook. Does "...ActivateNext" navigate to the previous active workbook...

At least in my testing, yes.

 

[craigexcel]

Though I still need to understand the code Jaafar wrote since I'm not experienced with all of that, I 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. However, if, after opening the 'utility' workbook, I run the Workbook_Open code, it switches to the correct workbook, even with > 2 workbooks open. Thought maybe Autpen was needed instead, but tried that without success.