Programmatically created form with name conflict

Question

I'm creating in Excel VBA a form using code. The following code snippet presents a problem in which the form is somehow created with the name already correctly set and then afterwards, in the only place where I set the said variable, it raises an issue saying that there is a form with that name (the variable in case).

Here is my code:

Dim frmName As String
frmName = "frm_" & Replace(CStr(Nome_do_formulario), " ", "")
Set myForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

With myForm
    .Properties("Caption") = Nome_do_formulario
    .Properties("Width") = 300
    .Properties("Height") = 270
    .Properties("Name") = frmName
End With

To be clear, the error is that when it reaches the line:

Set myForm = ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)

Somehow it already creates a form with name that's set after at the with statement:

  With myForm
        .Properties("Caption") = Nome_do_formulario
        .Properties("Width") = 300
        .Properties("Height") = 270
        .Properties("Name") = frmName '<- HERE
    End With

And then, when it tries to run the with statement it breaks and says that a form with that name already exists.

The whole thing is ran at another module as:

Public Sub Main()

       Dim ac As autoCrud
       Set ac = New autoCrud
       ac.CreateCRUDView     

End Sub

The form creation happens inside the ac.CreateCRUDView.

How is it pulling the name variable before it's set and then trying to use it to make another form with the same name?

Answer 1

VBE suffers heavily corruption when it is about UserForms collection in a VBA Project. Even if you remove explicitly a UserForm from your project, you might get errors creating programatically (and sometimes in the normal way) another with the same name.

Try using this approach:

Dim frmName As String
Dim myForm As VBComponent

frmName = "frm_" & Replace(CStr(Nome_do_formulario), " ", "")
ThisWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm).Name = frmName
Set myForm = ThisWorkbook.VBProject.VBComponents(frmName)

With myForm
    .Properties("Caption") = Nome_do_formulario
    .Properties("Width") = 300
    .Properties("Height") = 270
End With

Remember, if you delete the newly created userform and run this code with the same Nome_do_formulário value, you'll get an error.