Article updated on January 09, 2018

In this post I will demonstrate how to create checkboxes in nonempty rows.

VBA code

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 = 2 To LRow
    If Cells(cell, "A").Value <> "" Then
        CLeft = Cells(cell, "E").Left
        CTop = Cells(cell, "E").Top
        CHeight = Cells(cell, "E").Height
        CWidth = Cells(cell, "E").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

 

Sub RemoveCheckboxes()
Dim chkbx As CheckBox

For Each chkbx In ActiveSheet.CheckBoxes
    chkbx.Delete
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. 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.

Download excel file

Add checkboxes to a sheet.xlsm

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

Recommended posts

Copy selected rows (checkboxes) (2/2)

This post describes how to copy selected rows using checkboxes. In a previous post I showed you how to add/remove […]

How to use the SHEET function

The SHEET function returns the sheet number of the cell reference sheet. Formula in cell D3: =SHEET(A1) Excel Function Syntax […]