excel - 从用户窗体中删除动态添加的控件

Question

我有一个带有动态添加复选框的 Excel 用户表单。

我使用如下代码添加复选框:

Set chkBox = Me.Controls.Add("Forms.Checkbox.1", "Checkbox" & i)

我想删除所有这些复选框。

Dim j As Integer
'Remove all dynamically updated checkboxes
For Each cont In Me.Controls
    For j = 1 To NumControls
        If cont.Name = "Checkbox" & j Then
            Me.Controls.Remove ("Checkbox" & j)
        End If
    Next j
Next cont

我收到以下错误消息:

Answer 1

更好的方法可能是跟踪您创建的控件(例如在集合中)，并使用它来删除它们。

这样您的代码就不会绑定(bind)到名称格式，并且也可以应用于其他控件类型。

Private cbxs As Collection

Private Sub UserForm_Initialize()
    Set cbxs = New Collection
End Sub

' Remove all dynamicly added Controls
Private Sub btnRemove_Click()
    Dim i As Long
    Do While cbxs.Count > 0
        Me.Controls.Remove cbxs.Item(1).Name
        cbxs.Remove 1
    Loop
End Sub


' Add some Controls, example for testing purposes
Private Sub btnAdd_Click()
    Dim i As Long
    Dim chkBox As Control
    For i = 1 To 10
        Set chkBox = Me.Controls.Add("Forms.CheckBox.1", "SomeRandomName" & i)
        chkBox.Top = 40 + i * 20
        chkBox.Left = 20
        cbxs.Add chkBox, chkBox.Name  '- populate tracking collection
    Next

    ' Demo that it works for other control types
    For i = 1 To 10
        Set chkBox = Me.Controls.Add("Forms.ListBox.1", "SomeOtherRandomName" & i)
        chkBox.Top = 40 + i * 20
        chkBox.Left = 60
        cbxs.Add chkBox, chkBox.Name
    Next

End Sub