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.
Only make one checkbox to be selected with VBA code
Only make one checkbox to be selected with VBA code
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 Sub5. 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 Sub6. 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.
Related articles:
- 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!
