In this post, I will demonstrate a macro that creates checkboxes in nonempty rows, see checkboxes in column E in image above.

The button "Add Checkboxes" next to column E is assigned to macro Addcheckboxes(), click the button and the macro will be executed.

The same thing goes with the button "Remove Checkboxes" next to column E is assigned to macro RemoveCheckboxes(), click the button and that macro will be executed.

VBA code - Add checkboxes

'Name macro
Sub Addcheckboxes()

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

'Don't refresh or update screen while processing macro, this will make the macro quicker.
Application.ScreenUpdating = False

'Find last non empty cell in column A
LRow = ActiveSheet.Range("A" & Rows.Count).End(xlUp).Row

'Iterate through 2 to last non empty cell 
For cell = 2 To LRow
    
    'Check if cell in column A is not equal to nothing
    If Cells(cell, "A").Value <> "" Then

        'Save cell dimensions and coordinates of corresponding cell in column E to variables
        CLeft = Cells(cell, "E").Left
        CTop = Cells(cell, "E").Top
        CHeight = Cells(cell, "E").Height
        CWidth = Cells(cell, "E").Width

        'Create checkbox based on dimension and coordinates data from variables
        ActiveSheet.CheckBoxes.Add(CLeft, CTop, CWidth, CHeight).Select
        With Selection
            .Caption = ""
            .Value = xlOff
            .Display3DShading = False
        End With
    End If
Next cell

'Turn on screen refresh
Application.ScreenUpdating = True

End Sub

VBA code - Remove checkboxes

'Name macro
Sub RemoveCheckboxes()

'Declare variables and data types
Dim chkbx As CheckBox

'Iterate through all check boxes on active sheet
For Each chkbx In ActiveSheet.CheckBoxes

    'Remove checkbox
    chkbx.Delete

'Continue with next checkbox
Next

End Sub

Where to copy vba code?

1. Copy above code
2. Press Alt+F11 in excel
3. Insert a module
4. Paste code into code window
5. Return to excel

I have assigned the macros to two buttons: Add Checkboxes and Remove Checkboxes.

1. Go to Developer tab
2. Click "Insert Controls" button
3. Click "Button" button ;-)
4. Click and drag on worksheet to create the button on a sheet
5. Select a macro
6. Click OK!

In the next post I will describe how to copy selected rows to another sheet.

Next: Copy selected rows (checkboxes) (2/2)

Related articles

---

Open Excel files in a folder [VBA]

This tutorial shows you how to list excel files in a specified folder and create adjacent checkboxes, using vba. The […]

Toggle a macro on/off using a button

This post demonstrates using a single button (form control) to turn a macro on or off. VBA Macro I am […]