Over the holidays I saw some vba code for checkboxes from the book ‘More Excel Outside the Box’ by Bob Umlas.

(download my Excel file)

Bob’s Code

This is Bob Umlas. He is an Excel god.

Let’s examine his vba code.

It assigns checkbox status (checked or not) to column D and names each checkbox based on text from column F:

Sub Assigner()
For I = 1 to 10
Activesheet.Checkboxes(i).LinkedCell = Cells(i, 4).Address
Activesheet.Checkboxes(i).Characters.Text = Cells(i, 6).Value
Next
End Sub

This was the starting point for various procedures I ended up creating. Thanks Bob!

Task List

I created a simple non vba task list and then a more functional task list inspired by Bob’s vba.

The Easy Way

To cross off a finished item it’s easier to use a drop down list and conditional formatting. Quick to create but functionality is limited.

Tasks from movie ‘The Big Lebowski’ 🙂

The Complex Way

Why use vba? If you have a long list of items vba can automate tedious tasks such as:

• check all checkboxes
• uncheck all checkboxes
• rename checkboxes
• create new checkboxes
• align checkboxes
• delete checkboxes

Adding New Checkboxes

‘Add Checkboxes’ (top of column K) was fun (and frustrating) to create.

Sub AddCheckboxesStartingInCurrentCell()
Dim actrow As Integer, SettingAddCheckBoxes As Integer, CBcount As Integer
CBcount = ActiveSheet.CheckBoxes.Count
Range(“A” & CBcount + 2).Activate

SettingAddCheckBoxes = Range(“SettingAddCheckBoxes”).Value

For i = 1 To SettingAddCheckBoxes
actrow = ActiveCell.RowWith ActiveSheet.CheckBoxes.Add(Selection.Left, Selection.Top, Selection.Width, Selection.Height)
.Width = 80
.LinkedCell = Cells(actrow, 9).Address
End With
ActiveCell.Offset(1, 0).Activate
Next i

End Sub

Notice variable CBcount. It determines where to add the next checkbox. It’s hard coded to column A (you can change this) in the row of the checkbox count plus 2 (row 1 is header row and we want to go 1 row below the lowest checkbox).

Variable SettingAddCheckBoxes defines how many new checkboxes to add each time you click ‘Add Checkboxes’. It’s a variable stored in named range “SettingAddCheckBoxes” corresponding to cell N1.

The other sub procedures are shorter many using a similar structure like this to loop:

ActiveSheet.CheckBoxes.Select
Dim cb As CheckBox
                       
        For Each cb In Sheet2.CheckBoxes
            cb.Value = True
        Next cb
etc…..

Why?

Why did I build this? (I took me hours!). I enjoy building things and sometimes I just can’t get the idea out of my head. Once I build it then it leaves me alone 🙂 Also, it’s winter and I prefer Excel over  joining a bowling league.

Similar Post

I created something similar previously(post). However, I didn’t want to look as building the code from scratch is such good practice.

About Me

My name is Kevin Lehrbass. I’m a Data Analyst from Canada.

I love playing around in Excel. Here I proved that I’m only freakishly tall in certain countries.

In this post I made a face move around the screen and interact with cells. It’s my love of Excel, curiosity, long winters and level of caffeine that cause these things to happen. And I’m an  introvert most of the time!