get-digital-help.com

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 to open the Visual Basic Editor.
3. Right-click on your workbook in the Project Explorer, see image above.
4. Click "Insert".
5. Click "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. Click "Insert" button.
3. Click "Button" button ;-)
4. Click and drag on the worksheet and then release mouse button to create the button.
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)