代码之家 › 专栏 › 技术社区 › Eitel Dagnin

VBA-删除复选框并取消与单元格的链接

excel-vba vba excel

Eitel Dagnin · 技术社区 · 7 年前

https://ccm.net/faq/37428-excel-vba-how-to-create-multiple-checkboxes )这将在选定范围内创建复选框-这很好-但现在我想做相反的事情,即删除指定范围内的复选框。

创造复选框:

Sub Insert_chkbx_Link_Cell()
    Dim rngCel As Range
    Dim ChkBx As CheckBox

    For Each rngCel In Selection
        With rngCel.MergeArea.Cells
            If .Resize(1, 1).Address = rngCel.Address Then
                Set ChkBx = ActiveSheet.CheckBoxes.Add(.Left, .Top, .Width, .Height)
                With ChkBx
                    .Value = xlOff
                    .LinkedCell = rngCel.MergeArea.Cells.Address
                    With .Border
                    End With
                End With
            End If
        End With
    Next rngCel
End Sub

下面是一个子集,它将“取消链接/取消分配”单元格中的复选框:

Sub Un_Assign()
    For Each sht In ActiveWorkbook.Sheets
        For Each CheckBox In sht.CheckBoxes
            CheckBox.OnAction = ""
        Next CheckBox
    Next sht
End Sub

这样做的问题是,它不会对指定的范围这样做,而是会对每个工作表这样做。

删除他们:

Sub Remove_chkbx_Unlink_Cell()
    Dim rngCel As Range
    Dim ChkBx As CheckBox

    For Each rngCel In Selection
        With rngCel.MergeArea.Cells
            If .Resize(1, 1).Address = rngCel.Address Then
                ActiveSheet.CheckBoxes.Delete
            End If
        End With
    Next rngCel
End Sub

所以我加了一个 for each 内部循环 if 语句并尝试指定范围:

Sub Remove_chkbx_Unlink_Cell()
    Dim rngCel As Range
    Dim ChkBx As CheckBox

    For Each rngCel In Selection
        With rngCel.MergeArea.Cells
            If .Resize(1, 1).Address = rngCel.Address Then
                For Each ChkBx In rngCel
                    CheckBox.OnAction = ""
                Next ChkBx

                rngCel.CheckBoxes.Delete
            End If
        End With
    Next rngCel
End Sub

问题是我在 For Each ChkBx In rngCel 生产线:

类型不匹配

编辑

下面,我已经选择了空复选框的单元格,并且以与我“创建”复选框大致相同的方式,我要删除它们:即,选择一个单元格区域并删除该区域中的复选框。

1 回复 | 直到 6 年前

Pá´Ê 7 年前

若要删除特定范围内的复选框,只需循环检查工作表中的所有复选框,并在其出现时将其删除 .TopLeftCell 交叉点( Application.Intersect Method

Option Explicit

Public Sub TestDeleteCheckBoxes()
    'delete all CheckBoxes in A1:A10 in Sheet1
    DeleteCheckBoxes ThisWorkbook.Worksheets("Sheet1").Range("A1:A10")
End Sub

Public Sub DeleteCheckBoxes(Target As Range)
    Dim Cbx As CheckBox
    For Each Cbx In Target.Parent.CheckBoxes 'loop through all CheckBoxes on the worksheet
        If Not Intersect(Cbx.TopLeftCell, Target) Is Nothing Then
            Cbx.Delete 'delete it if it intersects with the target range
        End If
    Next Cbx
End Sub

注意,我将其包装到一个过程中,以便您可以在任何目标范围内重用代码。