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?
Best Office Productivity Tools
Supercharge Your Excel Skills with Kutools for Excel, and Experience Efficiency Like Never Before. Kutools for Excel Offers Over 300 Advanced Features to Boost Productivity and Save Time. Click Here to Get The Feature You Need The Most...
Office Tab Brings Tabbed interface to Office, and Make Your Work Much Easier
- Enable tabbed editing and reading in Word, Excel, PowerPoint, Publisher, Access, Visio and Project.
- Open and create multiple documents in new tabs of the same window, rather than in new windows.
- Increases your productivity by 50%, and reduces hundreds of mouse clicks for you every day!