Introducing the new (June 2024) Excel Checkboxes

This page has 0 threads | Add post

Blogs

Finding our blogs useful? Please consider helping us to share more!

Introducing the new (June 2024) Excel Checkboxes
You can now add a checkbox to a cell with a single mouse-click. They look good, although it's hard to ensure that only one checkbox in a range is selected.

Posted by Andy Brown on 03 July 2024

In this blog

Adding Checkboxes
Deleting checkboxes
Mutually exclusive checkboxes

You can now add checkboxes to an Excel worksheet:

Someone about to cast their vote in the UK General Election.

Someone casting their vote ...

Strictly speaking you've always been able to do this, but previously it was messy; now you just click on a button on the Insert tab of the ribbon (as shown below).

Note that the roll-out for this feature began on 26th June 2024, with all users on monthly updates on the Current Channel expected to get access by the end of July. You can fsee which update channel you are on here.

Adding Checkboxes

Here's how to add a checkbox to a cell:

You sense that more widgets may be coming to this Controls group some time soon!

You can change the value of a checkbox just by clicking on it (the cell containing the checkbox will hold the value 0 or 1, or true or false, depending on how you want to use this).

Deleting checkboxes

Removing checkboxes is a two-stage process. First you select the range and press Del:

The first deletion unsets the checkboxes in the selected range.

If you now press Del a second time, Excel removes the checkboxes:

This time round the checkboxes themselves will be removed.

Slightly odd!

Mutually exclusive checkboxes

In general you should only use checkboxes when a user can select multiple items in a list. For our example, we have a problem - in the UK you can only vote for one party. Here is a VBA macro which you could attach to the Change event of a worksheet to make the checkboxes mutually exclusive:

Private Sub Worksheet_Change(ByVal Target As Range)

'the vote chosen

Dim VoteChosen As Boolean

'the range of voting check boxes

Dim VoteRange As Range

'range holding the votes possible

Set VoteRange = Range("C4:C11")

'check if changed cells intersect with votes

If Intersect(VoteRange, Target) Is Nothing Then

'it doesn't - ignore

Exit Sub

End If

'if more than one cell changed, just show warning

If Target.Cells.Count > 1 Then

't change more than one vote at a time - please undo this", _

MsgBox "You can"

vbOKOnly + vbExclamation, "Error"

Exit Sub

End If

'otherwise, detect current value

VoteChosen = Target.Value

'if a NO vote, can exit; otherwise, deselect others

If VoteChosen Then

Application.EnableEvents = False

VoteRange.Value = False

Target.Value = VoteChosen

Application.EnableEvents = True

End If

End Sub

I would only use this code if you already know VBA, and if you already understand how to attach events to Excel objects.

You can see more about Excel checkboxes at this Microsoft blog, or at this (remarkably similar) help page.

Some other pages relevant to the above blog include:

Microsoft Excel training

This blog has 0 threads

Add a new post

BLOGS BY TOPIC

BLOGS BY AUTHOR

Adding Checkboxes

Deleting checkboxes

Mutually exclusive checkboxes

Head office

London

Manchester