Author: Oscar Cronquist Article last updated on February 24, 2018

This tutorial shows you how to list excel files in a specified folder and create adjacent checkboxes, using vba. The last macro opens selected files.

List files in a folder

The following macro is assigned to the "Refresh list" button. It lists all *.xls, *.xlsx and *.xlsm files in the folder specified in cell B1.

Sub ListFiles()
Dim cell As Range, selcell As Range
Dim Value As String
Dim Folder As Variant, a As Long
ReDim Folders(0)

Set cell = Range("A4")
Set selcell = Selection
Range("A4:B10000").Value = ""
Folderpath = Range("B1").Value

If Right(Folderpath, 1) <> "\" Then
  Folderpath = Folderpath & "\"
End If

Value = Dir(Folderpath, &H1F)

Do Until Value = ""
    If Value <> "." And Value <> ".." Then
        If GetAttr(Folderpath & Value) <> 16 Then
            If Right(Value, 4) = ".xls" Or Right(Value, 5) = ".xlsx" Or Right(Value, 5) = ".xlsm" Then
                cell.Offset(0, 0).Value = Value
                cell.Offset(0, 1).Value = FileLen(Folderpath & Value)
                Set cell = cell.Offset(1, 0)
            End If
        End If
    End If
Value = Dir

Call Addcheckboxes


End Sub

Recommended reading

Search all workbooks in a folder

Today I'll show you how to search all Excel workbooks with file extensions xls, xlsx and xlsm in a given folder for a […]

Add checkboxes in column C

Sub Addcheckboxes()
Dim cell, LRow As Single
Dim chkbx As CheckBox
Dim CLeft, CTop, CHeight, CWidth As Double
Application.ScreenUpdating = False
LRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row
For cell = 4 To LRow
    If Cells(cell, "A").Value <> "" Then
        CLeft = Cells(cell, "C").Left
        CTop = Cells(cell, "C").Top
        CHeight = Cells(cell, "C").Height
        CWidth = Cells(cell, "C").Width
        ActiveSheet.CheckBoxes.Add(CLeft, CTop, CWidth, CHeight).Select
        With Selection
            .Caption = ""
            .Value = xlOff
            .Display3DShading = False
        End With
    End If
Next cell

Application.ScreenUpdating = True
End Sub

Recommended reading

Search all workbooks in a folder and sub folders

Search all workbooks in a folder is a popular post, I am happy so many find it useful. rusl cato […]

Open selected files

The macro below is assigned to "Open selected files" button. It checks if a checkbox is enabled and opens the excel file.

Sub OpenFiles()
Dim Folderpath As String
Dim cell As Range
Dim r, LRow As Single
Dim CWB As Workbook

Folderpath = Range("B1").Value
Set CWB = ActiveWorkbook

If Right(Folderpath, 1) <> "\" Then
  Folderpath = Folderpath & "\"
End If

For Each chkbx In ActiveSheet.CheckBoxes
    If chkbx.Value = 1 Then
        For r = 1 To Rows.Count
            If Cells(r, 1).Top = chkbx.Top Then
                Workbooks.Open Filename:=Folderpath & Range("A" & r).Value
                Exit For
            End If
        Next r
    End If

End Sub

Download excel *.xlsm file

List all files in a folder.xlsm