I have a huge excel sheet but instead of having one checkbox in a cell, there are many checkboxes on top of each other, what would be the VB Code that only one check box remains and rest all others are deleted?

CodePudding user response：

Please, test the next code. It uses a dictionary to keep unique shapes to remain top cell addresses and all overlapping ones name will be placed in an array, which finally will be used to delete them:

Sub deleteDuplicateChkBOnTheSameCell()
    Dim chkB As MSForms.CheckBox, s As Shape, sh As Worksheet
    Dim arrDel, kS As Long, El, dict As Object
    
    Set sh = ActiveSheet
    ReDim arrDel(sh.Shapes.count) 'redim the array for maximum number of elemnts to be loaded
    
    'Place all check boxes TopLeftCell addres in a dictionary:
    Set dict = CreateObject("Scripting.Dictionary")
    For Each s In sh.Shapes
        If TypeName(s.OLEFormat.Object) = "CheckBox" Then 'Form CheckBox object...
            If Not dict.Exists(s.TopLeftCell.Address) Then
                dict.Add s.TopLeftCell.Address, vbNullString  'place the first found
            Else
                arrDel(kS) = s.name: kS = kS   1                         'next occurrences loaded in the array
            End If
        End If
    Next s
    
    If kS > 0 Then 'check if the code really found duplicates...
        ReDim Preserve arrDel(kS - 1)  'keep in the array only loaded elements
        Application.ScreenUpdating = False
         For Each El In arrDel                  'iterate between the shapes to be deleted name and do the job:
            sh.Shapes(El).Delete
         Next El
        Application.ScreenUpdating = True
    End If
End Sub

Please, check it and send some feedback.

If something not clear enough, do not hesitate to ask for clarifications...