In a form Click a Workbook name in listbox1 and see the Sheets of the WB in the Listbox2

Question

In a User form I would like to display two listboxs . In the listbox1 I would display the Opened Workbooks with the Exception of the Personal Workbook; and whenever I click one of the Workbooks in the Listbox1 I would like to display the Worksheets available in that workbook in the Listbox2. Doing some research I found the following code which represent how far I have been able to go about this:

Private Sub UserForm_Initialize()

    UserForm1.Caption = "Workbooks and Sheets"

    Dim wb As Workbook
    Dim n As Long

    For Each wb In Application.Workbooks
    ListBox1.AddItem wb.Name
    Next wb

    For n = 1 To ActiveWorkbook.Sheets.Count
    ListBox2.AddItem ActiveWorkbook.Sheets(n).Name

    Next n


End Sub

Also I found this Post in Here but I should be doing something wrong because when I run the code both list box appear without any content whatsoever. Do you know How could I get this code to Work? Thank you

Populate `ListBox1` with the workbook names when the form is initialised (you are currently doing that, but putting it into `ListBox2`). And then, in ListBox1's `Click` event, populate `ListBox2` with the relevant sheets (similar to what you are currently doing for `ListBox1` in the initialise, but iterating through the worksheets of the workbook that was selected rather than the worksheets of the `ActiveWorkbook`). — YowE3K, Nov 03 '17 at 03:15
FWIW - I just copied your current code into a UserForm (after adding two ListBox controls to the form) and ran the code, and it did exactly what I thought it would do - i.e. put the active workbook's sheet names into ListBox1 and the names of the open workbooks into ListBox2 — YowE3K, Nov 03 '17 at 03:20
Thank you for noticing the way I was using the code in the Listbox 1 and 2. I already edited my post with the corrections needed. My problem with that code is that when I click any other workbook name than the active workbook the selection doesn't show the sheets from the selected workbook name Unless a Close the form and select another workbook ad the active workbook. Thank you. — Sergio, Nov 03 '17 at 04:07

score 0 · Accepted Answer · answered Nov 03 '17 at 04:22

You should create a subroutine that will refresh both list. In this way, you can keep the open Workbook list up to date.

Private Sub ListBox1_Click()
    RefreshListBoxes
End Sub

Private Sub UserForm_Initialize()
    RefreshListBoxes
End Sub

Sub RefreshListBoxes()
    Dim wb As Workbook, ws As Worksheet

    ListBox2.Clear
    If ListBox1.ListIndex > -1 Then
        On Error Resume Next
        Set wb = Workbooks(ListBox1.Value)
        On Error GoTo 0

        If wb Is Nothing Then
            MsgBox "Workbook not found: " & ListBox1.Value, vbCritical, "Try Again"
        Else
            For Each ws In wb.Worksheets
                ListBox2.AddItem ws.Name
            Next
        End If
    End If

    ListBox1.Clear
    For Each wb In Workbooks
        If Not wb.FullName Like "*Excel\XLSTART\PERSONAL.XL*" Then ListBox1.AddItem wb.Name
    Next

End Sub

Oh my God, How did you do it?. I just tried you code in it Populate the Listbox 1 With all the opened workbooks in my screen Except with the Personal Workbook, This is so amazing Thank you so much! — Sergio, Nov 03 '17 at 23:35
If instead of using Listbox control I use a Listview Control I what way the code would change? Thank You — Sergio, Nov 11 '17 at 04:39

score 0 · Answer 2 · answered Nov 03 '17 at 04:22

Here's some code to get you started:

Option Explicit

Private Sub UserForm_Initialize()
    Dim wb As Workbook

    Me.Caption = "Workbooks and Sheets"

    For Each wb In Application.Workbooks
        ListBox1.AddItem wb.Name
    Next wb

    'Selecting item 0 (zero), i.e. the list's top element, will trigger its Click event,
    'which in turn will call ListWbWorksheets, populating ListBox2.
    ListBox1.Selected(0) = True
End Sub

Private Sub ListBox1_Click()
    Dim sWorkbookName As String

    sWorkbookName = ListBox1.List(ListBox1.ListIndex)
    ListWbWorksheets sWorkbookName
End Sub

Private Sub ListWbWorksheets(ByVal psWorkbookName As String)
    Dim targetWb As Excel.Workbook
    Dim n As Long

    Set targetWb = Application.Workbooks(psWorkbookName)
    ListBox2.Clear

    For n = 1 To targetWb.Sheets.Count
        ListBox2.AddItem targetWb.Sheets(n).Name
    Next n

    Set targetWb = Nothing
End Sub

Note that the code above doesn't check for any errors; @Thomas Inzina's answer at least checks whether a workbook's name leads to an actual workbook.

I will spend the next few days studying your code to try to figure how it works. It did exactly what I was looking for. Thank you so much — Sergio, Nov 03 '17 at 23:02
You can comfortably follow along by putting a breakpoint on the `Me.Caption = "Workbooks and Sheets"` line (click on the line and press F9). Then, launch your form; the code will pause at the breakpoint. From there, press F8 to execute the code line by line. You can hover the mouse pointer over any variable and its value will appear. Also look for other code stepping possibilities under the Debug menu. Have fun! — Excelosaurus, Nov 03 '17 at 23:27

In a form Click a Workbook name in listbox1 and see the Sheets of the WB in the Listbox2

2 Answers2

Linked