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

Remove Dynamically Added Controls from Userform

提问by ale10ander

I have an Excel userform with dynamically added checkboxes.

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

I add the checkboxes with code that looks like this:

我使用如下所示的代码添加复选框：

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

I want to remove all of these checkboxes.

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

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

I get the following error message:

我收到以下错误消息：

回答by chris neilsen

A better approach may be to keep track of the controls you create (eg in a collection), and use that to remove them.

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

This way your code is not bound to the name format, and can be applied to other control types too.

这样您的代码就不受名称格式的约束，也可以应用于其他控件类型。

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

回答by Gene Skuratovsky

Assuming there are no othe control names starting with "Checkbox",

假设没有以“Checkbox”开头的其他控件名称，

For Each cont In Me.Controls
    If InStr(cont.Name, "Checkbox") = 1 Then
        Me.Controls.Remove cont.Name
    End If
Next cont

回答by Patrick Lepelletier

if you already know the name of the controls, the type, and how many, why double loop ?

如果您已经知道控件的名称、类型和数量，为什么要使用双循环？

note that ONLY controls created at runtime can be removed.

请注意，只能删除在运行时创建的控件。

'the following removes all controls created at runtime
Dim i As Long
On Error Resume Next
With Me.Controls
    For i = .Count - 1 to 0 step -1
        .Remove i
    Next i
End With
Err.Clear: On Error GoTo 0

and for your case : 'if all naming are correct

对于您的情况：'如果所有命名都是正确的

Dim j&
For j = 1 To NumControls
    Me.Controls.Remove "Checkbox" & j
Next j

回答by ale10ander

Adding a check for the control seemed to fix this. Not entirely sure why, but it works.

添加对控件的检查似乎可以解决此问题。不完全确定为什么，但它有效。

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

回答by MrPantsMan

I rewrote the original code using command buttons, and just added "Me.Controls.Count" rather than "NumControls" and defined "Cont" as a Control. It seems to be working for me. Please let me know if this works for you:

我使用命令按钮重写了原始代码，并添加了“Me.Controls.Count”而不是“NumControls”并将“Cont”定义为控件。它似乎对我有用。请让我知道这是否适合您：

-->

-->

On Error Resume Next
Dim Cont As Control
Dim C As Integer
'Remove all dynamically updated checkboxes
For Each Cont In Me.Controls
    For C = 1 To Me.Controls.Count
    If Cont.Name = "CommandButton" & C Then
        Me.Controls.Remove ("CommandButton" & C)
    End If
    Next C
Next Cont

回答by bodgesoc

Another way to select which controls are kept and which are deleted is to use the .Tag attribute. This allows some fine control of the controls as they are added, for example by using the .Tag as a bitfield.

选择保留哪些控件以及删除哪些控件的另一种方法是使用 .Tag 属性。这允许在添加控件时对其进行一些精细控制，例如通过使用 .Tag 作为位域。

At creation time:

在创建时：

With Me.Controls.add("Forms.Label.1", Visible:=True)
{code}
    .Tag = 1
{more code}

Then when the time comes to tidy up:

然后到了整理的时候：

For Each C In Me.Controls
    If C.Tag = 1 Then Me.Controls.Remove C.Name
Next