How to only make one checkbox to be selected in a group of checkboxes in Excel?
As the below 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.
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?
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 agoYou really need to explain how to access the class name in order to change it. BTW folks, it's F4 to open the window on the left side of the screen and then edit the class name.
- To post as a guest, your comment is unpublished.· 9 months agohi,
the error appears under the first code.
xObj.Object.Value = False
- To post as a guest, your comment is unpublished.· 7 months agoHi Arnold, did you get any prompt box? and which Excel version are you using?
- To post as a guest, your comment is unpublished.· 2 months agoIt works fine if all you are using is checkboxes.
If you add any other activeX items and assign controls to them, it throws the error Arnold mentioned.
- To post as a guest, your comment is unpublished.· 1 years 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.· 1 years agoHi,
Please save the workbook as an Excel Macro-enabled Workbook in order to save the codes in workbook. But when you reopen the workbook, you need to get into the code window to manually run the code in the Module window to activate it.
- To post as a guest, your comment is unpublished.· 16 days agoHi, is there a way around? I would like to use this for multiple people, and I am sure they will not run the code aftre opening....
- To post as a guest, your comment is unpublished.· 3 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