How to Only Make One Checkbox to be Selected in a Group of Checkboxes in Excel?

In Excel, checkboxes are a handy way for users to choose from a list of options. However, there are times when you wish to limit the selection of a collection of checkboxes to just one checkbox at a time. When designing forms, surveys, or other situations where mutually exclusive choices must be made, this can be especially helpful.

In this tutorial, we'll look at how to use Excel to enforce a single checkbox selection. By the end of this course, you will be able to confidently apply the approaches to build groups of checkboxes where only one checkbox can be selected at a time, regardless of your level of familiarity with Excel. So, let's get started and discover how to simplify the choosing process by becoming an Excel checkbox selection limitation master!

Only Make One Checkbox to be Selected in a Group of Checkboxes

Here we will first create a class module, then create a VBA module, and then run it to complete the task. So let us see a simple process to know how you can only make one checkbox be selected in a group of checkboxes in Excel.

Step 1

Consider an Excel sheet where you have multiple check boxes.

First, right-click on the sheet name and select View code to open the VBA application.

Right Click > View Code.

Step 2

Then click on Insert and select Class Module, then copy the below code into the text box.

Insert > Class Module > Copy.

Code

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

Step 3

Then change the class name to ClsChk.

Step 4

Then again, click on Insert, select Module, and copy the below code to the text box.

Insert > Module > Copy.

Code

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

Step 5

Then click F5 to run the module. Then you will see that only one checkbox can be checked once.

Conclusion

In this tutorial, we have used a simple example to demonstrate how you can only select one checkbox in a group of checkboxes in Excel to highlight a particular set of data.