Uncheck last selected checkbox

Question

Please see file in google drive I have 2 tables but planning to add more. I want vba to unmark the last selected checkbox whether it be in table 1 or 2.

Each table has 4 columns starting with the checkboxes in column D, Column E would have commodity codes, column F will be Descriptions and column G would have Quantity. I have removed all linked cell properties to have a clean slate. I do have a code to link all checkboxes to its respective cell.

The problem I have is, the maximum allowed marked checkboxes should be 27, once the 28th checkbox is marked, it should be unmarked and a message box should appear giving the user a warning. I have tried utilizing ChatGpt but the code it provides either unmark the very first checkbox in table 1 or the last checked box in table 2. Any assistance would be highly appreciated. Code from ChatGpt below:

Private Sub Worksheet_Calculate()
Dim checkbox As checkbox
Dim checkedCheckboxes As Collection
Dim maxChecked As Integer
Dim totalChecked As Integer

Maximum number of checkboxes allowed in total
maxChecked = 27

Initialize the collection to store checked checkboxes
Set checkedCheckboxes = New Collection

Loop through each checkbox in the worksheet
For Each checkbox In Me.CheckBoxes
Check if the checkbox is checked
If checkbox.Value = xlOn Then
Add the checked checkbox to the collection
checkedCheckboxes.Add checkbox
End If
Next checkbox

Calculate the total count of checked checkboxes
totalChecked = checkedCheckboxes.Count

Check if the total count exceeds the maximum allowed
If totalChecked > maxChecked Then
Uncheck the last checked checkbox
checkedCheckboxes(1).Value = xlOff
checkedCheckboxes.Remove 1

Display a message
MsgBox "Reservation in SAP only has 27 lines. Please transfer data and clear checkboxes to continue!", vbInformation, "Result Check"
End If
End Sub

Answer 1

• I agree with @Tim's point, stop users' selection is easier.
• However if you prefer to uncheck the last one when it reaches the limit, please try ...

Note: Asssign macro CheckboxeEvent to all checkboxes.

Option Explicit
Dim OnDic As Object
Sub CheckboxeEvent()
    Dim oCB As CheckBox, OnCount As Long
    Dim oSht As Worksheet
    Const MAX_CHECKED As Long = 27
    Set oSht = Worksheets("Sheet1") ' modify as needed
    If OnDic Is Nothing Then
        Set OnDic = CreateObject("scripting.dictionary")
        For Each oCB In oSht.CheckBoxes
            If oCB.Value = xlOn Then OnDic(oCB.Name) = ""
        Next
    End If
    OnCount = 0
    For Each oCB In oSht.CheckBoxes
        If oCB.Value = xlOn Then OnCount = OnCount + 1
    Next oCB
    If OnCount > MAX_CHECKED Then
        For Each oCB In oSht.CheckBoxes
            If oCB.Value = xlOn Then
                If Not OnDic.exists(oCB.Name) Then
                     oCB.Value = xlOff
                End If
            End If
        Next
    Else
        OnDic.RemoveAll
        For Each oCB In oSht.CheckBoxes
            If oCB.Value = xlOn Then OnDic(oCB.Name) = ""
        Next
    End If
End Sub

Answer 2

Ideally you don't let your users select too many checkboxes in the first place - if they have to go back and unselect checkboxes before submitting then it's just making their life harder...

If you put this in a regular module and link all the checkboxes to it so a click runs the code then it will disable checkboxes once the limit is reached for that sheet:

Sub LimitCheckboxes()
    
    Const MAX_CHECKED As Long = 27
    Dim cb As CheckBox, colOn As Collection, colOff As Collection, atLimit As Boolean
    
    Set colOn = New Collection
    Set colOff = New Collection
    For Each cb In Sheet2.CheckBoxes
        If cb.Value = xlOn Then
            colOn.Add cb
        Else
            colOff.Add cb
        End If
    Next cb
    
    atLimit = (colOn.Count = MAX_CHECKED)
    For Each cb In colOff
        cb.Interior.Color = IIf(atLimit, RGB(200, 200, 200), vbWhite)
        cb.Enabled = Not atLimit
    Next cb

End Sub