How To Allow Only One Checkbox to Be Checked in Excel per Table

[MsNoobie]

Hello , So I'm a newbie to VBA, and i basically have an Excel sheet, in which 6 table are created . 3 of these tables have three checkboxes. 2have 4 checkboxes and 1 have 7 check boxes.
Now, My issue here is. I want for each Table. Whenever the User checks one box, the other two option (or 3 options, depends on the Table) to hide. meaning, he will only be able to check one box for each table .
I tried it Many times using a YouTube video. but I got just one table to work. whenever I start with the Other Tables, it doesn't work. Can anyone help me with this Issue ? . Any Ideas.
This is the Code i got from the YouTube Tutorial .
Class Checkbox (This is the First Part of the code and should be placed inside Class Module code section)



Option Explicit

Public WithEvents Allow1CheckBox As MSForms.CheckBox

Private Sub Allow1CheckBox_Click()

Call SelOneCheckBox(Allow1CheckBox)

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

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

xObj.Object.Enabled = True

End If

Next

End If

End Sub





Module (This is the second part of the code and should be placed inside Module code section)





Dim xCollection As New Collection

Public Sub CheckBoxClass_Init()

Dim xSht As Worksheet

Dim xObj As Object

Dim xAllow1CheckBox As CheckBoxClass

Set xSht = ActiveSheet

Set xCollection = Nothing

For Each xObj In xSht.OLEObjects

If xObj.Name Like "CheckBox**" Then

Set xAllow1CheckBox = New CheckBoxClass

Set xAllow1CheckBox.Allow1CheckBox = CallByName(xSht, xObj.Name, VbGet)

xCollection.Add xAllow1CheckBox

End If

Next

Set xAllow1CheckBox = Nothing

End Sub







Workbook code (This is the third part of the code and should be placed inside ThisWorkBook code section)



Private Sub Workbook_Open()

On Error Resume Next

CheckBoxClass_Init

End Sub



Private Sub Workbook_SheetActivate(ByVal Sh As Object)

On Error Resume Next

CheckBoxClass_Init

End Sub



Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

On Error Resume Next

CheckBoxClass_Init

End Sub

 

[bobsan42]

Assuming you are using actual Table objects, try this:
remove or disable/comment your previous code.
put this code in a standard code module:

Sub clickOnACheckbox()
    Dim sh As Worksheet
    Dim tb As ListObject
    Dim tbName As String, tbiName As String
   
    Dim cb As CheckBox, cbi As CheckBox
    Dim i As Long
   
    Set sh = ActiveSheet
    Set cb = sh.CheckBoxes(Application.Caller)
    If cb.Value = xlOff Then GoTo ex
    i = cb.Index
    Set tb = cb.TopLeftCell.ListObject
    If Not tb Is Nothing Then tbName = tb.Name
    On Error Resume Next
    For Each cbi In sh.CheckBoxes
        If i <> cbi.Index Then
            tbiName = ""
            tbiName = cbi.TopLeftCell.ListObject.Name
            If tbName = tbiName Then
                cbi.Value = xlOff
            End If
        End If
    Next cbi
   
ex:
    Set sh = Nothing
    Set tb = Nothing
    Set cb = Nothing
    Set cbi = Nothing
End Sub

Then add this sub and run it to assign the above macro to all checkboxes on the ActiveSheet.

Sub assignMacroToAllCheckboxes()
    Dim cb As CheckBox
    For Each cb In ActiveSheet.CheckBoxes
        cb.OnAction = "clickOnACheckbox"
    Next cb
   
    Set cb = Nothing
End Sub

click-them-away
also works on boxes outside of tables.