I often use check-boxes in my Excel videos to explain the solution steps. Creating them can be tedious so I thought that there had to be an easier way.

Earlier this year I thought about using VBA (macros) to automate this but I got distracted and forgot about it. I kept using a simple template with preset check-boxes that required a lot of customizing.

A few weeks later the idea came back and it seemed like a good challenge. I had a day off back in late March and I spent most of the day playing around with VBA until I got the code to work!

What Are The Requirements?

• Type in the solution description. VBA code will add a check box to the left
• Code should skip any blank cells (not add check-box)
  
• Dynamic check-box label (‘Step’ or ‘Task’) with sequential counter
• Easily check or uncheck all of the check-boxes
• Quickly delete all check-boxes

Here Is A Pic Of The Solution

Was It Easy To Create The Code?

No. I’m not a professional VBA programmer. However, I have tinkered around with VBA for years. This code took me 2+ hours to create! But the challenge was too interesting to give up! The looping part was especially tricky. But when I finally got the code to work it felt great! I would say that the feeling of accomplishment was more important than the practical application of conditionally adding check boxes.

Here Is My VBA Code

Sub DynamicallyInsertCheckBoxes()

Dim Chkbxname As String, labelnum As Single ‘used to create checkbox label
Dim counter, howmanysteps As Single                 ‘these relate to counting and looping
Dim chkbxLeft, chkbxTop, chkbxHeight, chkbxWidth As Double  ‘position, height and width
Dim lastRow As Long                                               ‘determine the last row with step description text

Call deletechkboxes

Range(“I2:I100”).Value = “”
lastRow = ActiveSheet.Range(“K” & Rows.Count).End(xlUp).Row ‘clear area in column I and K

Sheet4.Activate                                                           ‘to ensure that we add check boxes in correct sheet
Application.ScreenUpdating = False                      ‘turn off screen updating to make it run faster

Chkbxname = Range(“Checkboxtext”).Value       ‘named range value = checkbox name. Could be ‘Step’, ‘Part’ etc.
countsteps = WorksheetFunction.CountA(Range(“k2:k100”))     ‘count steps (non blanks) in column K

labelnum = 1
For counter = 2 To lastRow + 1                    ‘start looping based on last cell with text in column K

If Cells(counter, “k”).Value <> “” Then      ‘if cell is blank then skip else prepare size and position
chkbxLeft = Cells(counter, “I”).Left
chkbxTop = Cells(counter, “I”).Top
chkbxHeight = Cells(counter, “I”).Height
chkbxWidth = Cells(counter, “I”).Width

ActiveSheet.CheckBoxes.Add(chkbxLeft + chkbxWidth / 1.7 – 15, chkbxTop, chkbxWidth, chkbxHeight).Select ‘create checkboxs

With Selection
.Caption = Chkbxname & labelnum    ‘add checkbox name
.Value = xlOn                                           ‘preset checkbox to off or on
.LinkedCell = “I” & counter                  ‘set linked cell for checked / not checked value
End With
labelnum = labelnum + 1                      ‘we just added a checkbox so increase checkbox label number

Else: labelnum = labelnum                  ‘if cell was blank then don’t increase checkbox label number
End If
Next counter

Range(“A1”).Activate
Application.ScreenUpdating = True
End Sub

Download My Excel File

You can download it here. The file extension is .xlsm as it has vba code (macros).

Watch My YouTube Video

About Me

My name is Kevin Lehrbass. I live in Markham, Ontario, Canada.  I’ve been studying, supporting, building, troubleshooting, teaching and dreaming in Excel since 2001. I’m a Data Analyst.

Hundreds of millions of people use Excel everyday. Thousands of hours can be saved and costly errors can be avoided or corrected if you study this powerful software. Check out my videos and blog posts.

Away from Excel I enjoy playing with my dog Cali (she doesn’t like Excel), learning Spanish and playing Chess.

Save