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)
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:
For I = 1 to 10
Activesheet.Checkboxes(i).LinkedCell = Cells(i, 4).Address
Activesheet.Checkboxes(i).Characters.Text = Cells(i, 6).Value
This was the starting point for various procedures I ended up creating. Thanks Bob!
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.
Dim actrow As Integer, SettingAddCheckBoxes As Integer, CBcount As Integer
CBcount = ActiveSheet.CheckBoxes.Count
Range(“A” & CBcount + 2).ActivateSettingAddCheckBoxes = Range(“SettingAddCheckBoxes”).ValueFor 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
Next iEnd 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:
Dim cb As CheckBox
For Each cb In Sheet2.CheckBoxes
cb.Value = True
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.
I created something similar previously(post). However, I didn’t want to look as building the code from scratch is such good practice.
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!