VBA: Checkboxes in Excel Cells
Prerequisite Courses
Have you ever found yourself in a situation where you need to insert checkboxes in cells? This is not a native function of Excel. Checkboxes are a form object and are designed to sit atop a spreadsheet rather than be contained in cells.
Several weeks ago a situation presented itself and I wanted to place check boxes in a column to the left of my data. I initially thought I could probably look online for a solution. After a pretty exhausting search, I found there were a lot of questions about how to do this but no answers.
If you have been following my other modules. I have covered using the macro recorder, using loops, inserting columns, and working with variables. I used all of the above and a little algebra (made my hurt, man high school was long time ago) to create the checkbox macro.
I was initially unfamiliar with the code that I needed to insert a checkbox and make it the appropriate size, so I recorded the task with macro recorder and later used the code in my macro.
What the Macro Assumes
The macro assumes that the block of text you want to insert checkboxes next to begins with Cell "A1". It will continue to insert checkboxes until it encounters a blank cell.
The Actual Action Taken
- Two new Columns are inserted to left of column "A"
- Checkboxes are inserted on top of each cell in the newly created column "A"
- Column "B" is linked to the checkbox in the corresponding row.
- The Width of Column "A" is changed to fit the check box.
- Column "B" is hidden.
Note: The value of the cell in column "B" is initially blank, if the box is checked it changes to "true", or if it is checked and then unchecked it changes to "false". If you desire you can later reference column "B" with another macro that has instructions for carrying out some action on checked items.
Sub checkboxes()
'define variable
Dim MyTop As Long
Dim i As Long
'insert new column for checkboxes and linked cell
Columns("A:A").EntireColumn.Insert
Columns("A:A").EntireColumn.Insert
i = 1
'loop to insert checkboxes
Do Until Len(Cells(i, 3)) = 0
MyTop = 12.75 * (i - 1)
ActiveSheet.checkboxes.Add(1, MyTop, 1, 1).Select
With Selection
.Caption = ""
.LinkedCell = "B" & i
.Display3DShading = False
End With
i = i + 1
Loop
'hide column with linked cells
Columns("B:B").Hidden = True
'Change width column "A" to fit checkbox
Columns("A:A").ColumnWidth = 2.43
End Sub
The End Result
Ready to Proceed?
Congratulations! You have learned to do quite a bit with VBA in a short time lets take a break and review some of the advanced functions available in excel. The next module cover vlookup, click here to proceed.
