Author: Oscar Cronquist Article last updated on May 24, 2019

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

The animated image below shows the user typing the folder name in cell B1, click "Refresh list" button and cell range A4:B6 is populated with file names, file sizes and checkboxes.

The checkboxes allow the user to select workbook files to open, the selected workbooks are opened as soon as the user clicks on "Open selected files" button located on the worksheet.

What you will learn in this article

  • How to insert buttons on the worksheet.
  • How to assign macros to the buttons.
  • How to use macros.
  • How to list files and file sizes from a given folder on a worksheet using VBA.
  • How to insert checkboxes programmatically.
  • How to open selected files programmatically.
  • How to save VBA code to your workbook.

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.

'Name macro
Sub ListFiles()

'Dimension variables and declare data types
Dim cell As Range, selcell As Range
Dim Value As String
Dim Folder As Variant, a As Long
ReDim Folders(0)

'Save cell A4 to object cell
Set cell = Range("A4")

'Save selected cell (range) to variable selcell
Set selcell = Selection

'Clear cell range A4:B10000
Range("A4:B10000").Value = ""

'Save value in cell B1 to variable Folderpath
Folderpath = Range("B1").Value

'Check if variable Folderpath ends with \ (backslash)
If Right(Folderpath, 1) <> "\" Then

  'Add a backslash if missing
  Folderpath = Folderpath & "\"
End If

'Return first file name in path specified in variable Folderpath
Value = Dir(Folderpath, &H1F)

'Continue iterate through files in folder until it is empty
Do Until Value = ""

    'Make sure file name is not . (current directory) or .. (parent directory)
    If Value <> "." And Value <> ".." Then

        'Make sure file name is not a folder
        If GetAttr(Folderpath & Value) <> 16 Then

            'Make sure file name ends with .xls or .xlsx or .xlsm
            If Right(Value, 4) = ".xls" Or Right(Value, 5) = ".xlsx" Or Right(Value, 5) = ".xlsm" Then
                'Save file name and file size to cell A4 and cell B4 respectively
                cell.Offset(0, 0).Value = Value
                cell.Offset(0, 1).Value = FileLen(Folderpath & Value)

                'Move to cell below 
                Set cell = cell.Offset(1, 0)
            End If
        End If
    End If
'Continue with next file
Value = Dir

'Go back to Do Until

'Start macro Addcheckboxes
Call Addcheckboxes

'Select the same cells that were selected when the macro started

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 to column C

'Name macro
Sub Addcheckboxes()

'Dimension variables and declare data types
Dim cell, LRow As Single
Dim chkbx As CheckBox
Dim CLeft As Double, CTop As Double, CHeight As Double, CWidth As Double

'Stop Excel screen from updating, this makes the macro a lot faster
Application.ScreenUpdating = False

'Delete all old checkboxes on active worksheet

'Save row number of cell containing non-empty values to variable LRow
LRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

'Iterate through number 4 to number stored in variable LRow
For cell = 4 To LRow

    'Make sure cell is not empty
    If Cells(cell, "A").Value <> "" Then

        'Save the number of pixels between cell in column C based on the row number stored in variable cell and left edge
        CLeft = Cells(cell, "C").Left

        'Save the number of pixels between cell in column C based on the row number stored in variable cell and top
        CTop = Cells(cell, "C").Top

        'Save the height (number of pixels) of cell in column C based on the row number stored in variable cell
        CHeight = Cells(cell, "C").Height
        'Save the cell width (in pixels) of cell in column C based on the row number stored in variable cell
        CWidth = Cells(cell, "C").Width

        'Insert and select checkbox based on left, top, width and height
        ActiveSheet.CheckBoxes.Add(CLeft, CTop, CWidth, CHeight).Select

        'Remove text and make sure checkbox is not checked
        With Selection
            .Caption = ""
            .Value = xlOff
            .Display3DShading = False
        End With
    End If
Next cell

'Show Excel screen changes
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.

'Name macro
Sub OpenFiles()

'Dimension variables and declare data types
Dim Folderpath As String
Dim cell As Range
Dim r As Single, LRow As Single
Dim CWB As Workbook

'Save value in cell B1 to variable Folderpath 
Folderpath = Range("B1").Value

'Save Active Workbook to object CWB
Set CWB = ActiveWorkbook

'Check if last character in folder path is not a backslash
If Right(Folderpath, 1) <> "\" Then

  'Add backslash
  Folderpath = Folderpath & "\"
End If

'Iterate through checkboxes located on active worksheet
For Each chkbx In ActiveSheet.CheckBoxes

    'Check if checkbox is enabled
    If chkbx.Value = 1 Then

        'Count cells above checkbox in order to open the right file
        For r = 1 To Rows.Count

            'Check if pixel count is the same between cell and checkbox
            If Cells(r, 1).Top = chkbx.Top Then

                'Open workbook based on name in cell
                Workbooks.Open Filename:=Folderpath & Range("A" & r).Value

                'Stop macro
                Exit For
            End If

        'Continue with next row number
        Next r

        'Activate workbook
    End If

End Sub

Where to put the macros?

  1. Press Alt + F11 to open the Visual Basic Editor.
  2. Click "Insert" on the top menu.
  3. Click "Module" to insert a code module. It will appear in the project explorer, see image above.
  4. Copy above VBA code and paste to the code module.
  5. Exit VB Editor and return to Excel.
Note, make sure you save your workbook with file extension *.xlsm in order to keep the code you pasted.

Download Excel file through email

Enter your email address to receive the workbook. Note, an email will actually be sent to you.
* You will also get a weekly newsletter, unsubscribe anytime!