VBA - How to find most recently active window?

HedgePig

Board Regular
Joined
Jun 27, 2002
Messages
146
Hello

I'm trying to find out how determine which workbook window was active immediately prior to the current one. Excel must know this because if I close the active window, it then activates the window that was active immediately prior to that.

I can't find anything in the windows collection object which will help me - the index number seems to refer to the order in which they were created, not active.

Any help appreciated!
HedgePig
 

Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
Would this do?

Code:
Private Sub Workbook_WindowDeactivate(ByVal Wn As Window)
    MsgBox Wn.Parent.Name
End Sub
 
Upvote 0
Andrew

Thank you for the suggestion but I don't think it would work (but I may be wrong) for the following reason: wouldn't that code need to be inserted into every workbook that was open, so as to keep track of which window was last selected? If, so that's no good for my purposes. However, if trapping the deactivate event (whatever window was deactivated) could be contained in a single module, then I think that your suggestion would work just fine. But I don't think this can be done?

I'm really trying to write a utility which, when invoked, will do some sort of a comparison between the two most recently selected workbooks - and it's determining what these workbooks are which has me stumped!
Thanks again for the suggestion, though.

HedgePig



[/quote]
 
Upvote 0
Hi,

This can definitely be done, probably by using application level events. You can use a single piece of code to track things happening at the application level. Take a look here for a good description. If you have any problems implementing this then please post back.
 
Upvote 0
You can do it with Application Level events:

Code:
'Class Module named ClassApp
Public WithEvents App As Application

Private Sub App_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window)
    MsgBox Wb.Name
End Sub

'General Module
Dim X As New ClassApp

Sub InitializeApp()
    Set X.App = Application
End Sub

The procedure InitializeApp must be run for it to work. You could do this from the Workbook_Open event procedure.
 
Upvote 0
You could do this by using the Application objects events. To do this-

1) Insert a new Class Module. Select it, press F4 and change the (Name) property to AppClass.

2) Copy and paste the following code into the Class Module-

Code:
Public WithEvents xlApp As Application

Private Sub xlApp_WindowDeactivate(ByVal Wb As Workbook, ByVal Wn As Window)

MsgBox Wb.Name

End Sub

3) Insert a standard module and copy and paste in the following-

Code:
Dim AppEvents As New AppClass

Public Sub InitializeAppClass()

Set AppEvents.xlApp = Application

End Sub

4) Run the InitializeAppClass sub (you could run this from the Workbook_Open event to initialize the class if you like).

Now, whenever you deactivate any workbook you can pick up its name.

Edit: Too slow :LOL:
 
Upvote 0
dk, Andrew, MudFace - thanks all for the suggestion! I should be able to get my code to do what I want now.

HedgePig

P.S. I still feel that somewhere the information I want might be diectly available without me having to trap it. But maybe that's a combination of laziness and optimism on my part.
 
Upvote 0
Hi,

I have created an addin that returns the list of all the workbooks that have been used/activated during the current Excel session.( That includes the last one :wink: )


The code will eventually add a new Menu Button under the Window Menu in the worksheet main menu to show a sorted list of all previous open workbooks.

The only requirement is that you have to be using Excel2000 or later !


So let's get started:


Open a new Blank workbook.

Insert a UserForm1 in your project and add the following controls in the stated order:


-1 Frame Frame1

-3 Labels inside the frame, namely Label2 and Label3
(Don't worry about the design and sizing of the controls you are adding because the code will take care of that later on !)

-3 other labels Outside the Frame , namely Label4, Label5 and Label6.

-1 CommandButton CommandButton1 outside the frame.

-1 Image Control Image1 also outside the Frame.

After completing the adding of the controls, Double click the Userform1 and paste the following code:




<font face=Courier New><SPAN style="color:#00007F">Option</SPAN> <SPAN style="color:#00007F">Base</SPAN> 1

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> CommandButton1_Click()
    Unload Me
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> UserForm_Initialize()
    <SPAN style="color:#00007F">Const</SPAN> FormHeight = 250
    <SPAN style="color:#00007F">Const</SPAN> LblsTop = 0
    <SPAN style="color:#00007F">Const</SPAN> LblsWidth = 140
    <SPAN style="color:#00007F">Const</SPAN> LblsHeight = 60
    <SPAN style="color:#00007F">Const</SPAN> CanButnWidth = 1
    <SPAN style="color:#00007F">Const</SPAN> CanButnHeight = 2
    Me.Label4.Caption = "Workbook  Name " & vbCrLf & "---------------" & vbCrLf
    Me.Label5.Caption = "Date  & Time  Activated" & vbCrLf & "-----------------" & vbCrLf
    Me.Label6.Caption = "Total Times Activated So Far " & vbCrLf & "-----------------"
    arTransp = WorksheetFunction.Transpose(arNvigtDetails)
    Label1.Left = 6: Label4.Left = Label1.Left + 1.5 * Frame1.Left
    Label2.Left = Label1.Left + LblsWidth: Label5.Left = Label4.Left + LblsWidth
    Label3.Left = Label1.Left + (LblsWidth * 2): Label6.Left = Label4.Left + (LblsWidth * 2)
    Label1.Caption = ""
    Label2.Caption = ""
    Label3.Caption = ""
    <SPAN style="color:#00007F">For</SPAN> h = <SPAN style="color:#00007F">UBound</SPAN>(arTransp) <SPAN style="color:#00007F">To</SPAN> 1 <SPAN style="color:#00007F">Step</SPAN> -1
        Label1.Caption = Label1.Caption & arTransp(h, 1) & vbCrLf
        Label2.Caption = Label2.Caption & arTransp(h, 2) & vbCrLf
        Label3.Caption = Label3.Caption & arTransp(h, 3) & vbCrLf
    <SPAN style="color:#00007F">Next</SPAN> h
    <SPAN style="color:#00007F">With</SPAN> Me
        .Width = FormWidth
        .Height = FormHeight
        .Caption = "Workbooks Navigation Log"
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cntr <SPAN style="color:#00007F">In</SPAN> Me.Controls
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">TypeOf</SPAN> Cntr <SPAN style="color:#00007F">Is</SPAN> Msforms.Label <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">With</SPAN> Cntr
                .AutoSize = <SPAN style="color:#00007F">True</SPAN>
                .WordWrap = <SPAN style="color:#00007F">True</SPAN>
                .TextAlign = fmTextAlignCenter
                .Font.Bold = <SPAN style="color:#00007F">True</SPAN>
                .ForeColor = vbRed
                .Top = Frame1.Top - .Height
            .Width = LblsWidth
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> Cntr <SPAN style="color:#00007F">In</SPAN> Me.Frame1.Controls
        <SPAN style="color:#00007F">If</SPAN> <SPAN style="color:#00007F">TypeOf</SPAN> Cntr <SPAN style="color:#00007F">Is</SPAN> Msforms.Label <SPAN style="color:#00007F">Then</SPAN>
            <SPAN style="color:#00007F">With</SPAN> Cntr
                .AutoSize = <SPAN style="color:#00007F">True</SPAN>
                .Font.Bold = <SPAN style="color:#00007F">False</SPAN>
                .ForeColor = vbBlack
                .TextAlign = fmTextAlignCenter
                .Top = LblsTop
                .Width = LblsWidth
            <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">If</SPAN>
    <SPAN style="color:#00007F">Next</SPAN>
    <SPAN style="color:#00007F">With</SPAN> Frame1
        .ScrollBars = fmScrollBarsVertical
        .ScrollHeight = Label1.Height
        .Height = Me.InsideHeight / 3
        .Width = LblsWidth * 3
        .Left = Me.InsideWidth / 2
        .Caption = ""
        Me.Width = .Width + LblsWidth * 3 / 4
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    <SPAN style="color:#00007F">With</SPAN> CommandButton1
        .Top = Me.InsideHeight - 3 / 2 * (.Height)
        .Left = (Me.InsideWidth / 2) - (.Width / 2)
        .Width = Label1.Width * 3 / 4
        .Height = Me.Height / 12
        .Accelerator = "C"
        .Caption = "Cancel"
        .Cancel = <SPAN style="color:#00007F">True</SPAN>
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
    stdole.StdFunctions.SavePicture NewCtrl.Picture, "Iconbmp"
    <SPAN style="color:#00007F">With</SPAN> Image1
        .Picture = LoadPicture("Iconbmp")
        .Left = 1
        .Top = 1
        .BorderStyle = fmBorderStyleNone
    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>



Now, add the following code in the ThisWorkbook Module:



<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">WithEvents</SPAN> app <SPAN style="color:#00007F">As</SPAN> Application
<SPAN style="color:#00007F">Public</SPAN> WndwActivations <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> app_WorkbookActivate(<SPAN style="color:#00007F">ByVal</SPAN> Wb <SPAN style="color:#00007F">As</SPAN> Workbook)
    <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> arNvigtDetails(1 <SPAN style="color:#00007F">To</SPAN> 3, 1 <SPAN style="color:#00007F">To</SPAN> WndwActivations + 2)
    <SPAN style="color:#00007F">ReDim</SPAN> <SPAN style="color:#00007F">Preserve</SPAN> arCountTimes(1 <SPAN style="color:#00007F">To</SPAN> WndwActivations + 2)
    arCountTimes(WndwActivations + 2) = <SPAN style="color:#00007F">CStr</SPAN>(Wb.Name)
    arNvigtDetails(1, WndwActivations + 2) = Wb.Name: arNvigtDetails(2, WndwActivations + 2) = _
    Format(Date, "dd-mmm-yy") & "   @   " & Time: arNvigtDetails(3, WndwActivations + 2) = _
    <SPAN style="color:#00007F">UBound</SPAN>(Filter(arCountTimes, Wb.Name)) + 1
    WndwActivations = WndwActivations + 1
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Private</SPAN> <SPAN style="color:#00007F">Sub</SPAN> Workbook_BeforeClose(Cancel <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Boolean</SPAN>)
    Application.CommandBars. _
    FindControl(ID:=30009).Reset
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

<SPAN style="color:#00007F">Sub</SPAN> Workbook_Open()
<SPAN style="color:#00007F">Set</SPAN> Me.app = Application
WndwActivations = 1
<SPAN style="color:#00007F">Set</SPAN> NewCtrl = Application.CommandBars. _
FindControl(ID:=30009).Controls.Add(Type:=msoControlButton, Temporary:=True)
<SPAN style="color:#00007F">With</SPAN> NewCtrl
    .Style = msoButtonIconAndCaption
    .Caption = "Workbooks Navigation Log"
    .OnAction = "ShowForm"
    .FaceId = 577
    .BeginGroup = <SPAN style="color:#00007F">True</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN>
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>

</FONT>


Finally insert a Standard Module in your project and add the following code:



<font face=Courier New><SPAN style="color:#00007F">Public</SPAN> arNvigtDetails() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
<SPAN style="color:#00007F">Public</SPAN> arCountTimes() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">String</SPAN>
<SPAN style="color:#00007F">Public</SPAN> arTransp() <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>
<SPAN style="color:#00007F">Public</SPAN> NewCtrl <SPAN style="color:#00007F">As</SPAN> CommandBarControl
<SPAN style="color:#00007F">Public</SPAN> <SPAN style="color:#00007F">Sub</SPAN> ShowForm()
    UserForm1.Show
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>


Now all you have to do is press Alt+F11 to go back to the Excel window click on SaveAs under the File Menu , give a name to the workbook, save the workbook as an Addin by selecting the FileFormat from the bottom DropDown List and Close Excel.

When you open Excel again, Click on Tools¦Add-ins¦Browse to locate your new Add-in and click Ok.This will install the Add-In so it is always available when you start Excel.

Once installed,Click on the Window Menu and you will find a Custom Menu that says "Workbooks Navigation Log".Click on this custom menu and a Form will show with some nice details about all the workbooks that are and were ! running during the current Excel session.

Experiment by adding new workbooks and navigating through them.


Let me know if any probs.
 
Upvote 0

Forum statistics

Threads
1,213,520
Messages
6,114,099
Members
448,548
Latest member
harryls

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top