Results 1 to 8 of 8

VBA - How to find most recently active window?

This is a discussion on VBA - How to find most recently active window? within the Excel Questions forums, part of the Question Forums category; Hello I'm trying to find out how determine which workbook window was active immediately prior to the current one. Excel ...

  1. #1
    Board Regular
    Join Date
    Jun 2002
    Location
    Shanghai, China
    Posts
    144

    Default VBA - How to find most recently active window?

    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

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    64,510

    Default Re: VBA - How to find most recently active window?

    Would this do?

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

  3. #3
    Board Regular
    Join Date
    Jun 2002
    Location
    Shanghai, China
    Posts
    144

    Default Re: VBA - How to find most recently active window?

    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]

  4. #4
    dk
    dk is offline
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sydney, Australia
    Posts
    2,932

    Default Re: VBA - How to find most recently active window?

    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.

  5. #5
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    64,510

    Default Re: VBA - How to find most recently active window?

    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.

  6. #6
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Sunny, spring-like Hull
    Posts
    3,339

    Default Re: VBA - How to find most recently active window?

    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

  7. #7
    Board Regular
    Join Date
    Jun 2002
    Location
    Shanghai, China
    Posts
    144

    Default Re: VBA - How to find most recently active window?

    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.

  8. #8
    Board Regular Jaafar Tribak's Avatar
    Join Date
    Dec 2002
    Location
    Larache--Morocco
    Posts
    4,950

    Default Re: VBA - How to find most recently active window?

    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 )


    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:




    Option Base 1

    Private Sub CommandButton1_Click()
    ****Unload Me
    End Sub

    Private Sub UserForm_Initialize()
    ****Const FormHeight = 250
    ****Const LblsTop = 0
    ****Const LblsWidth = 140
    ****Const LblsHeight = 60
    ****Const CanButnWidth = 1
    ****Const 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 = ""
    ****For h = UBound(arTransp) To 1 Step -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
    ****Next h
    ****With Me
    ********.Width = FormWidth
    ********.Height = FormHeight
    ********.Caption = "Workbooks Navigation Log"
    ****End With
    ****For Each Cntr In Me.Controls
    ********If TypeOf Cntr Is Msforms.Label Then
    ************With Cntr
    ****************.AutoSize = True
    ****************.WordWrap = True
    ****************.TextAlign = fmTextAlignCenter
    ****************.Font.Bold = True
    ****************.ForeColor = vbRed
    ****************.Top = Frame1.Top - .Height
    ************.Width = LblsWidth
    ************End With
    ********End If
    ****Next
    ****For Each Cntr In Me.Frame1.Controls
    ********If TypeOf Cntr Is Msforms.Label Then
    ************With Cntr
    ****************.AutoSize = True
    ****************.Font.Bold = False
    ****************.ForeColor = vbBlack
    ****************.TextAlign = fmTextAlignCenter
    ****************.Top = LblsTop
    ****************.Width = LblsWidth
    ************End With
    ********End If
    ****Next
    ****With Frame1
    ********.ScrollBars = fmScrollBarsVertical
    ********.ScrollHeight = Label1.Height
    ********.Height = Me.InsideHeight / 3
    ********.Width = LblsWidth * 3
    ********.Left = Me.InsideWidth / 2
    ********.Caption = ""
    ********Me.Width = .Width + LblsWidth * 3 / 4
    ****End With
    ****With 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 = True
    ****End With
    ****stdole.StdFunctions.SavePicture NewCtrl.Picture, "Iconbmp"
    ****With Image1
    ********.Picture = LoadPicture("Iconbmp")
    ********.Left = 1
    ********.Top = 1
    ********.BorderStyle = fmBorderStyleNone
    ****End With
    End Sub




    Now, add the following code in the ThisWorkbook Module:



    Public WithEvents app As Application
    Public WndwActivations As Integer

    Private Sub app_WorkbookActivate(ByVal Wb As Workbook)
    ****ReDim Preserve arNvigtDetails(1 To 3, 1 To WndwActivations + 2)
    ****ReDim Preserve arCountTimes(1 To WndwActivations + 2)
    ****arCountTimes(WndwActivations + 2) = CStr(Wb.Name)
    ****arNvigtDetails(1, WndwActivations + 2) = Wb.Name: arNvigtDetails(2, WndwActivations + 2) = _
    ****Format(Date, "dd-mmm-yy") & "** @** " & Time: arNvigtDetails(3, WndwActivations + 2) = _
    ****UBound(Filter(arCountTimes, Wb.Name)) + 1
    ****WndwActivations = WndwActivations + 1
    End Sub

    Private Sub Workbook_BeforeClose(Cancel As Boolean)
    ****Application.CommandBars. _
    ****FindControl(ID:=30009).Reset
    End Sub

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




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



    Public arNvigtDetails() As Variant
    Public arCountTimes() As String
    Public arTransp() As Variant
    Public NewCtrl As CommandBarControl
    Public Sub ShowForm()
    ****UserForm1.Show
    End Sub



    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.
    Office/Excel 2007 Win XP

    Common sense is not so common.


    http://photo-larache.blogspot.com/

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com