Populate combobox with all open workbook names?

[jkeyes]

Does anyone have some quick code for populating a combobox with the full names (i.e. wkbk1.xls) of all open workbooks?

Thanks for any and all help!

 

[sbendbuckeye]

Hello,

Dim wkb As Workbook
For Each wkb In Workbooks
    If wkb.Visible Then
        YourComboBox.Items.Add(wkb.Name)
    End If
Next wkb
Set wkb = Nothing

The visible check is because you want to, presumably, skip Personal.xls, etc. Good Luck!

 

[jkeyes]

Thanks for the reply, and sorry for the delay (this project got put on hold)...

So I tried your code... I put it under the Sheet selection Change event, added a Control ComboBox to the sheet, and named it cbOpenFiles (I adjusted the code for this):

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Dim wkb As Workbook
    For Each wkb In Workbooks
        If wkb.Visible Then
            cbOpenFiles.Items.Add (wkb.Name)
        End If
    Next wkb
    Set wkb = Nothing
End Sub

But, I'm getting a Run-time Error 438, Object doesn't support this property or method, and the debugger is highlighting the If wkb.Visible line.

Also, I meant to ask that the name of this current workbook be excluded from the ComboBox list...

Any ideas on what I'm doing wrong or need to do to fix? Thanks again!

 

[sbendbuckeye]

Sorry, you have to iterate the Windows collection instead of the Workbooks collection. I usually work with the Sheets collection and I always forget this .

Dim win As Window
For Each win In Windows
    If win.Visible = True Then
        If win.Parent.Name <> ActiveWorkbook.Name Then
            ' Do your processing here
            cbOpenFiles.Items.Add win.Parent.Name
        End If
    End If
Next Window
Set win = Nothing

Good Luck!