Adding list of open workbooks to combo-box and related worksheets to a list-box (VBA Excel)

I am working on a userform where a combobox gives user the names of every excel workbook that are already opened at that moment.

I want to put the names of all worksheets taking place in the workbook that is selected in the combobox on a listbox and I want it to be dynamic -that is, as the user selects another workbook from the combobox, the names (of the worksheets) appearing in the listbox should be automatically changing.

However, I can't figure out how I can access to the inventory of the combobox and make the desired additions. Any help/comment is appreciated. Thank you.

Solution

You can use the following functions

This one is for the UserForm, when it initializes it will fill in the combobox with all open sheets

Private Sub UserForm_Initialize()

    Dim i As Long

    For i = 1 To Workbooks.Count

        ComboBox1.AddItem (Workbooks(i).Name)
    Next

End Sub

This is about the Combobox itself, whenever you select some workbook from the combobox this function will be called and it is filling the list box with the sheets of that workbook.

Private Sub ComboBox1_Change()

    Dim selected_wb As Workbook

    Set selected_wb = Workbooks(ComboBox1.Text)

    ListBox1.Clear

    For Each ws In selected_wb.Worksheets

         ListBox1.AddItem ws.Name

    Next ws

End Sub

This function is called when you click the sheet name from the listbox, it will select the sheet and will close the user form

Private Sub ListBox1_Click()

    Dim selected_wb As Workbook
    Dim selected_ws As Worksheet

    Set selected_wb = Workbooks(ComboBox1.Text)
    Set selected_ws = selected_wb.Sheets(ListBox1.Text)

    selected_ws.Activate
    UserForm1.Hide


End Sub