ActiveX - Checkbox

The ActiveX Checkbox can be placed directly onto a worksheet.
Display the "Developer" tab, Controls group and select from the Insert drop-down.

alt text

Draw the control onto the worksheet.

alt text

When an ActiveX control is added to a worksheet it is actually embedded and becomes an Object of the worksheet.

Shortcut Menu

When you right mouse click on this control the following shortcut menu will be displayed.

alt text

Linking to a Cell

To link the status of the checkbox with the value in a cell you need to add some VBA code.
Double click on the control.
This will open the Visual Basic Editor and insert the corresponding checkbox "Click" event.
This event will be added to the code module of the active worksheet.

alt text

There is also a "LinkedCell" property that can be used.
You can use this property if you do not want to double click and add the VBA code.
The LinkedCell approach gives you less flexibility and always puts TRUE or FALSE in the cell.

alt text

Changing the Font and Size of the Text

This only changes the font size of the text, not the size of the square box.
Right mouse click and select "Properties" to display the Properties popup window.
Find the Font property and click on the button on the right hand side to change the font.

alt text

Changing the size of the Square Box

There is an option called "move and size with cells" on the Format Control dialog box but this cannot be used to change the size of the square box.

alt text

An alternative to this is to display a tick symbol inside the actual cells, combined with some VBA code.
In this example we are adding 10 check boxes to the cell range "B2:B10".
This approach adds "1" or "0" to the adjacent cells in column "C".

alt text

Add the following code to the code module of the worksheet.
Change the value of "sCheckBoxesRanges" to be the cell range you want to add check boxes to.
Run the "AddCheckboxes" subroutine once to initialise the cells.

Const sCheckBoxesRange As String = "B2:B10" 

Private Sub AddCheckboxes()
    With Range(sCheckBoxesRange)
        .Value = Chr(163)
        .Font.Name = "Wingdings 2"
        .Font.Size = 25
        .Offset(0, 1).Value = 0
    End With
End Sub

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    If (Target.Cells.Count = 1) Then
        If Not Intersect(Range(sCheckBoxesRange), Target) Is Nothing Then
            If Target.Value = Chr(163) Then
                Target.Value = Chr(82)
                Cells(Target.Row, Target.Column + 1).Value = 1
                Target.Value = Chr(163)
                Cells(Target.Row, Target.Column + 1).Value = 0
            End If
            Cells(Target.Row, Target.Column + 1).Select
        End If
    End If
End Sub

© 2022 Better Solutions Limited. All Rights Reserved. © 2022 Better Solutions Limited TopPrevNext