Non-FormControl Checkbox in Excel with VBA

Introduction

A while ago, my friend came up with this code, to create a checkbox in Excel, without using form controls. The idea was very simple, and surprisingly, the VBA code is very simple as well. It is a very clever solution, to avoid form controls.

Assumptions

This article assumes that:

• You know a little VBA or Visual Basic
• You know how to create macros in Excel
• You know how to open up the macro editor (Alt-F11)

Using the Code

The code has two components.

Component One is the function that will turn a cell into a 3 step checkbox:

1. Checked Positive
2. Checked Negative
3. Un-Checked

Component Two is a definition on the worksheet, which cells should turn into checkboxes.

Component One

Create a new module in the files VBA Project, or simply add it to your PERSONAL.XLS(X), then simply add the code below to the module.

What it will do, is when it is fired, it will check what is the character that is in the cell, and jump to the next stage according to that. It is taking advantage of the Wingdings font where:

So what we are doing if the cell is containing ü if yes then switch to û, and if it contains û the clear the cell.

As a tweak, the code changes the font color to green for the ü and red for the û.

Finally, we make sure the cell changes its font style to Wingdings.

Sub CellCheckBox()

If ActiveCell.FormulaR1C1 = "ü" Then
    ActiveCell.FormulaR1C1 = "û"
    ActiveCell.Font.ColorIndex = 3 ' OPTIONAL: Font color
ElseIf ActiveCell.FormulaR1C1 = "û" Then
    ActiveCell.FormulaR1C1 = ""
Else
    ActiveCell.FormulaR1C1 = "ü"
    ActiveCell.Font.ColorIndex = 10 ' OPTIONAL: Font color
End If
    
ActiveCell.Font.Name = ("Wingdings")
ActiveCell.Font.Size = 12 ' OPTIONAL: Font size
ActiveCell.Font.Bold = True ' OPTIONAL: Font boldness
       
End Sub 

Now we only have to create a trigger event. We do that by adding the next component to the worksheet where you want the checkboxes to be located.

Component Two

To add the next code, double-click on the worksheet in the Visual Basic Editor where you want to add the checkbox to appear.

We are going to use the "BeforeDoubleClick" event, and we are doing it for the following reason:

• We do not want the checkbox to become editable when people double-click it.

Now what this Sub does is, it takes the Target which is where you double-clicked, and fires Component One. We also added the cancel, which makes it possible, that the cell will not become editable, with a flashing cursor.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Now we are going to check if one call or multiple cells were clicked:

If Target.Cells.Count > 1 Then Exit Sub

Next we are going to set, in which of the cells we want Component One to fire in. If the target "Intersects" the range we provided (the intersection is not empty), then it will fire Component one.

You have to add the range to the Range("First Cell", "Last Cell") part in the code. You can see it filled with an example below:

If Not Intersect(Target, Range("B3", "B18")) Is Nothing Then
   Cancel = True ' Prevents the cell to become editable
   Run "CellCheckBox" ' Runs Component One
End If

And of course:

End Sub  

Here is the code in one:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
If Target.Cells.Count > 1 Then Exit Sub
    
If Not Intersect(Target, Range("B3", "B18")) Is Nothing Then
    Cancel = True
    Run "CellCheckBox"
End If

End Sub 

If you have separate ranges, you will need to add the second IF section as many times as needed. For example:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
    
If Target.Cells.Count > 1 Then Exit Sub
    
If Not Intersect(Target, Range("B3", "B18")) Is Nothing Then ' RANGE 1
        Cancel = True
        Run "TaskTick"
    End If
    
If Not Intersect(Target, Range("D3", "D18")) Is Nothing Then ' RANGE 2
        Cancel = True
        Run "TaskTick"
    End If
    
If Not Intersect(Target, Range("F3", "F18")) Is Nothing Then ' RANGE 3
        Cancel = True
        Run "TaskTick"
    End If
    
If Not Intersect(Target, Range("H3", "H18")) Is Nothing Then ' RANGE 4
        Cancel = True
        Run "TaskTick"
    End If 

End Sub 

Final Words

I think this is a very simple and fun little macro that you can use to brighten up Excel files. You can also use this for other thing than checkboxes, maybe to indicate statuses with other pictograms of Wingdings, Webdings, etc...

If you have any questions, feel free to contact me!

History

• Article version 1.0