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(), press with left mouse button on the button and the macro will be rund.

The same thing goes with the button "Remove Checkboxes" next to column E is assigned to macro RemoveCheckboxes(), press with left mouse button on the button and that macro will be rund.

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 to open the Visual Basic Editor.
3. Press with right mouse button on on your workbook in the Project Explorer, see image above.
4. Press with left mouse button on "Insert".
5. Press with left mouse button on "Module".
6. Paste VBA code to the module.
7. Exit VBE and return to Excel.

I have assigned the macros to two buttons: "Add Checkboxes" and "Remove Checkboxes", the top image shows these buttons.

1. Go to Developer tab.
2. Press with left mouse button on "Insert" button.
3. Press with left mouse button on "Button" button ;-)
4. Press with left mouse button on and drag on the worksheet and then release mouse button to create the button.
5. Select a macro.
6. Press with left mouse button on OK!

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

Get the Excel file

Add-checkboxes-to-a-sheet.xlsm

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

Check boxes category

Copy selected rows (checkboxes) (2/2)
This article demonstrates a macro that copies selected rows based on enabled check boxes. The image above shows data on […]

Multi-level To-Do list template
Today I will share a To-do list excel template with you. You can add text to the sheet and an […]

Open Excel files in a folder [VBA]
This tutorial shows you how to list excel files in a specific folder and create adjacent checkboxes, using VBA. The […]

Excel categories

Comments (8)