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 in the year I thought about using VBA (Excel macros) to automate this but I got distracted and then 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
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
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
.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
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
Application.ScreenUpdating = True
Download My Excel File
Watch My YouTube Video
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 at McKinsey & Company.
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.