Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
562 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
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. |
You can now add checkboxes to an Excel worksheet:
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.
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).
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!
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:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.