To post as a guest, your comment is unpublished.· 7 months 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.
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.
- Reuse Anything: Add the most used or complex formulas, charts and anything else to your favorites, and quickly reuse them in the future.
- More than 20 text features: Extract Number from Text String; Extract or Remove Part of Texts; Convert Numbers and Currencies to English Words.
- Merge Tools: Multiple Workbooks and Sheets into One; Merge Multiple Cells/Rows/Columns Without Losing Data; Merge Duplicate Rows and Sum.
- Split Tools: Split Data into Multiple Sheets Based on Value; One Workbook to Multiple Excel, PDF or CSV Files; One Column to Multiple Columns.
- Paste Skipping Hidden/Filtered Rows; Count And Sum by Background Color; Send Personalized Emails to Multiple Recipients in Bulk.
- Super Filter: Create advanced filter schemes and apply to any sheets; Sort by week, day, frequency and more; Filter by bold, formulas, comment...
- More than 300 powerful features; Works with Office 2007-2019 and 365; Supports all languages; Easy deploying in your enterprise or organization.
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.· 9 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