Cannot delete a control from a userform using VBA

Hello:

I have made a good-faith attempt to search the forum for the answer but am unable to find a workable solution so please bear with me.

Here is the situation:

- I have a userform with a number of ActiveX controls: command buttons, labels, etc.
- While trying to duplicate some of them, I end up with some with the generic label CommandButton1, Label3, etc.
- I wish to delete these extraneous controls using VBA but run into a problem

My code looks like this:

Sub Cleanup_Userform()

    Dim badControl As MSForms.Control
    Dim controlNum As Integer

    controlNum = 0
    For Each badControl In MyUserForm.Controls
        Debug.Print controlNum & " - " & badControl.Name
        controlNum = controlNum + 1

        If Left(badControl.Name, 7) = "Command" Or _
            Left(badControl.Name, 5) = "Combo" Or _
            Left(badControl.Name, 5) = "Frame" Or _
            Left(badControl.Name, 5) = "Image" Or _
            Left(badControl.Name, 5) = "Label" Or _
            Left(badControl.Name, 7) = "TextBox" Then MyUserForm.Controls.Remove (badControl)
    Next badControl

End Sub

When I use the variable badControl as shown above, I get the error: "Run-time error '438': Object doesn't support this property of method"

When I use the index number (as in: MyUserForm.Controls.Remove (controlNum - 1) suggested by one forum post), I get the error: "Run-time error '444': Could not delete the controls. This method can't be used in this context"

The answer is probably quite trivial but I can't figure out what I am missing.

Thanks for your assistance.

The help on Remove says,

This method deletes any control that was added at run time. However, attempting to delete a control that was added at design time will result in an error.

Rather than delete could you not just make them invisible?

Administrative Note:

Welcome back to the forum.

We would very much like to help you with your query, however you need to include code tags around your code.

Please take a moment to add the tags. Posting code between [code] [/code] tags makes your code much easier to read and copy for testing, and it also maintains VBA formatting.

Please see Forum Rule #2 about code tags and adjust accordingly. Click on Edit to open your post, then highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here

(Note: this change is not optional. By way of a reminder, I've done it for you today.)

If this is a one-off clean up operation, you should be using the Designer property of the relevant VBComponent - for example:

Sub Cleanup_Userform()

    Dim badControl As MSForms.Control
    Dim controlNum As Integer
   
   Dim des As Object
   Set des = ThisWorkbook.VBProject.VBComponents("MyUserform").designer ' change the name of the form as needed
   
    controlNum = 0
    For Each badControl In des.Controls
        Debug.Print controlNum & " - " & badControl.Name
        controlNum = controlNum + 1

        If Left(badControl.Name, 7) = "Command" Or _
            Left(badControl.Name, 5) = "Combo" Or _
            Left(badControl.Name, 5) = "Frame" Or _
            Left(badControl.Name, 5) = "Image" Or _
            Left(badControl.Name, 5) = "Label" Or _
            Left(badControl.Name, 7) = "TextBox" Then des.Controls.Remove badControl.Name
    Next badControl

End Sub

You would need to enable access to the VBA project in the Trust Center in macro settings, or just delete them yourself.

Glad we could help, and thanks for the rep.