How to only make one checkbox to be selected in a group of checkboxes in Excel?
As the left screenshot shown, for a group of checkboxes listing in row 2, when selecting or checking only one checkbox, the other checkboxes will be disabled. How to achieve it? The VBA code in this article can help you.
Recommended Productivity Tools for Excel
Office Tab: Bring powerful tabs to Office (include Excel), just like Chrome, Safari, Firefox and Internet Explorer. Save you half the time, and reduce thousands of mouse clicks for you. 30-day Unlimited Free Trial
Kutools for Excel: Save 71% of your time and solve 82% Excel problems for you. 300+ advanced tools designed for 1500+ work scenario, make Excel much easy and increase productivity immediately.60-day Unlimited Free Trial
You can run the below VBA codes to only select one checkbox in a checkbox group per time. Please do as follows.
1. Firstly, please insert the checkboxes as you need. Here, you should insert the ActiveX Control check boxes as following screenshot shown:
2. Then press Alt + F11 keys simultaneously to open the Microsoft Visual Basic for Applications window.
3. In the opening Microsoft Visual Basic for Applications window, click Insert > Class Module.
4. Change the class name to ClsChk in the (Name) box of the Properties pane, and then copy and paste the below VBA code into the corresponding Code window. See screenshot:
VBA code 1: Only select one checkbox per time
Option Explicit Public WithEvents Chk As MSForms.CheckBox Private Sub Chk_Click() Call SelOneCheckBox(Chk) End Sub Sub SelOneCheckBox(Target As Object) Dim xObj As Object Dim I As String Dim n As Integer If Target.Object.Value = True Then I = Right(Target.Name, Len(Target.Name) - 8) For n = 1 To ActiveSheet.OLEObjects.Count If n <> Int(I) Then Set xObj = ActiveSheet.OLEObjects.Item(n) xObj.Object.Value = False xObj.Object.Enabled = False End If Next Else I = Right(Target.Name, Len(Target.Name) - 8) For n = 1 To ActiveSheet.OLEObjects.Count If n <> Int(I) Then Set xObj = ActiveSheet.OLEObjects.Item(n) xObj.Object.Enabled = True End If Next End If End Sub
5. Now click Insert > Module, then copy and paste the below VBA code into the Module window.
VBA code 2: Only select one checkbox per time
Dim xCollection As New Collection Public Sub ClsChk_Init() Dim xSht As Worksheet Dim xObj As Object Dim xChk As ClsChk Set xSht = ActiveSheet Set xCollection = Nothing For Each xObj In xSht.OLEObjects If xObj.Name Like "CheckBox**" Then Set xChk = New ClsChk Set xChk.Chk = CallByName(xSht, xObj.Name, VbGet) xCollection.Add xChk End If Next Set xChk = Nothing End Sub
6. Press the F5 key to run the code.
From now on, when checking any one of the checkbox in the worksheet, the other checkboxes will be disabled automatically, and you can uncheck it to activate all checkboxes again.
Note: If new checkbox is added to the checkbox group, please rerun the VBA code to activate all checkboxes again. Deleting checkbox from the checkbox group need to rerun the code as well.
- How to filter data based on checkbox in Excel?
- How to hide checkbox when row is hidden in Excel?
- How to highlight cell or row with checkbox in Excel?
- How to create a drop down list with multiple checkboxes in Excel?
- How to insert date stamp into a cell if ticked a checkbox in Excel?
Recommended Productivity Tools
You are guest ( Sign Up? )
or post as a guest, but your post won't be published automatically.
To post as a guest, your comment is unpublished.· 2 months agowhen i close the Excel and open the file again the checkboxes stop working how do I fix this pls?
To post as a guest, your comment is unpublished.· 2 years agoHi! Thanks for this useful guide! I have an additional question: Im working with both columns and rows. If one checkbox value is true, all checkboxes in the same row and column has to be disabled. How do I do that? Best, Morten