Excel VBA runtime error '-2147319767 (80028029)'

Question

Hey all I have a quite weird problem with my VBA code. I use macros to add/remove userform controls programmatically. When I simply add a userform control and after that try to remove it, everything works fine. But as soon as I try to remove userform controls I just created and USED (just like userform.Show) I receive the titled error. After trying again it works ... so first question be like, what the hell is this? And second question would be how can I solve this or do I even need to solve it? Could I just run over this error with some kind of error handling?

I'm not sure what part of my code helps to understand my problem, but I assume the code to remove the userform controls could help:

Public Function delete_userform_controls(strUserForm As String)
    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim iCount As Integer
    Dim cnt As Control
    Dim iMaxColumns As Integer
    Dim lCountOfLines As Long

    Set VBProj = ActiveWorkbook.VBProject
    Set VBComp = VBProj.VBComponents(strUserForm)

    For Each cnt In VBComp.Designer.Controls
        If TypeName(cnt) = "Label" Then iMaxColumns = iMaxColumns + 1
    Next cnt

    With VBComp.Designer
        For iCount = 0 To iMaxColumns - 1
            .Controls.Remove ("label_" & iCount & "")
            .Controls.Remove ("textbox_" & iCount & "")
        Next
        .Controls.Remove ("ok_button")
        .Controls.Remove ("cancel_button")
    End With

    With VBComp.CodeModule
        .DeleteLines 1, .CountOfLines
    End With
End Function

And just in addition, if I don't user any of the userfoms I can add and remove controls as much as I want, no errors ... thx!

Solution

I suspect you are adding controls at design time, but once the userform is loaded you are trying to remove controls at runtime, which is not allowed. When you see the error for the first time and choose [End], you are resetting the VBA environment, which unloads the UserForm. (Other things happen too: all global variables lose their values.) At that point, the userform is no longer loaded, so you can delete controls.

If this is your situation, then you need to make sure the userform is loaded before adding controls. When you do userform.Show for the first time, two things happen: The form is loaded, then it is made visible. You can load it without making it visible with the .Load method. Then you can add your controls. So long as you don't unload the form with the .Unload method (or reset VBA), your controls will persist. .Hide and .Show will change the form's visibility, but won't cause controls added at runtime from being discarded.

On the other hand, if you really want to add controls at design-time so that they persist (and are saved when the workbook is saved), then you need to use .Unload to unload the form before removing controls.