VBA keep track of which workbooks are open

Question

How can VBA keep track of which workbooks are open?

I am writing a data mining macro that takes information from a variable number of workbooks. The user may choose what workbooks will be parsed via a userform. However, workbooks can be opened and closed while the userform is running.
So, how can my userform keep track of which workbooks are open, so that it can display them accurately.

Right now, I'm using a recursive function that calls itself with "Application.OnTime". I don't really like this solution for because it involves extra checks to see if the userform is still open, as well as having a delay because of whatever period the function is called at.

Final: Combined solution from answers and comments
userform code, requires a ListBox called WorkbookList, and a Textbox called FileTextBox

Private WithEvents App As Application

Public Sub WorkbookList_UpdateList()
    WorkbookList.Clear

    For Each Wb In Application.Workbooks
        WorkbookList.AddItem Wb.name
    Next Wb
End Sub

Private Sub WorkbookList_Change()
    If WorkbookList.ListIndex = -1 Then Exit Sub

    key = WorkbookList.List(WorkbookList.ListIndex)

    For Each Wb In Application.Workbooks
        IsWorkBookOpen Wb.path
        If Wb.name = key Then FileTextbox.text = Wb.path
    Next Wb
End Sub

Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
    WorkbookList_UpdateList
End Sub

Private Sub App_NewWorkbook(ByVal Wb As Workbook)
    WorkbookList_UpdateList
End Sub

Private Sub App_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    Application.OnTime Now + TimeValue("00:00:01"), "WorkbookClosed"
End Sub

Private Sub UserForm_Initialize()
    Set App = Application
    WorkbookList_UpdateList
    UpdatePeriodicly
End Sub

module code (put this in a vba module):

'Code From: http://www.ozgrid.com/forum/showthread.php?t=152892
Function IsUserFormLoaded(ByVal UFName As String) As Boolean
    Dim UForm As Object

    IsUserFormLoaded = False
    For Each UForm In VBA.UserForms
        If UForm.name = UFName Then
            IsUserFormLoaded = True
            Exit For
        End If
    Next
End Function

Public Sub WorkbookClosed()
    If IsUserFormLoaded("InputForm") = False Then Exit Sub
    InputForm.WorkbookList_UpdateList
End Sub

You can use Application Events fgor this. E.g. see cpearson.com/excel/appevent.aspx — Tim Williams
It's really a matter of how often you need to refresh that list. If you want as close to real-time as possible, use the event handler as Tim suggests, and have that handler simply refresh your listbox with the names from the Workbooks collection, each time a new workbook opens up (or closes). — David Zemens
Use OnTime in the app-level workbook events. Set it for 1 second later. The collection object will be updated by then. — Dick Kusleika

Tim Williams Tim Williams · Accepted Answer · 2014-07-24T19:53:02

You can use Application Events fgor this.

E.g. see cpearson.com/excel/appevent.aspx

Private WithEvents app As Excel.Application

Sub Init()
    Set app = Application 'start capturing events
End Sub


Private Sub app_NewWorkbook(ByVal Wb As Workbook)
    Debug.Print "New"
End Sub

Private Sub app_WorkbookBeforeClose(ByVal Wb As Workbook, Cancel As Boolean)
    Debug.Print "Before close: " & Wb.Name
End Sub

Private Sub app_WorkbookOpen(ByVal Wb As Workbook)
    Debug.Print "Open: " & Wb.Name
End Sub

VBA keep track of which workbooks are open

2 Answers