Populate combobox with all open workbook names?

jkeyes

Active Member
Joined
Apr 15, 2003
Messages
343
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!
 

Some videos you may like

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a $25,000 loan, 5% annual interest, 60 month loan.

sbendbuckeye

Active Member
Joined
Nov 26, 2003
Messages
440
Hello,
Code:
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

Active Member
Joined
Apr 15, 2003
Messages
343
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):

Code:
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

Active Member
Joined
Nov 26, 2003
Messages
440
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 :(.
Code:
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!
 

Watch MrExcel Video

Forum statistics

Threads
1,114,090
Messages
5,545,897
Members
410,711
Latest member
Josh324
Top