How can I exclude .xlsb file types in my ComboBox?

82 views Asked by At

I have a Userform that is essentially two combo boxes that are populated by the open workbooks. Is there a way I can exclude PERSONAL.XLSB from this list? This what I have:

Sub UserForm_Initialize()
Dim wkb As Workbook
With Me.ComboBox1
    For Each wkb In Application.Workbooks
        .AddItem wkb.Name
    Next wkb
End With
With Me.ComboBox2
    For Each wkb In Application.Workbooks
        .AddItem wkb.Name
    Next wkb
End With
End Sub
1

There are 1 answers

1
teylyn On BEST ANSWER

try

With Me.ComboBox1
    For Each wkb In Application.Workbooks
        If Not Right(wkb.Name, 4) = "xlsb" Then
            .AddItem wkb.Name
        End If
    Next wkb
End With

If you don't want to rely on the file extension being visible, you can check the file format instead.

If Not wkb.FileFormat = 50 Then
   .AddItem wkb.Name
End If