Search code examples
formsexcelmodelessvba

Openning a Modeless Form at runtime - VBA Excel

I'll try to explain the situation:

I have a form, which is generated and designed programmatically.

So, I have an object instanced with this form: object "NewForm"

At some point, I need to show this form. So I have to add the object NewForm as a form:

VBA.UserForms.Add (NewForm.Name)

And then show the form, 

UserForms(0).Hide
UserForms(0).Show

The problem is that I need to show a modeless form, so normally a simple

UserForms(0).Hide
UserForms(0).Show vbModeless

would be enought. However, when I use this last line of code, the form shows up and suddenly disappears.

I've also tried to set the property ShowModal as False, but I have the same result.

Thank you in advance,

Best regards

NOTE: I've tried using NewForm.Name.Show, and it doesn't work, so I have to use the collection instead.

Solution

  • It depends how you execute the code. I tried a very simple test creating a dummy form called NewForm with nothing inside and executed the following code from the Immediate window of the VB editor:

    Sub test_NewFrom()
    Dim mForm As NewForm
    Set mForm = New NewForm
    mForm.Show (vbModeless)
End Sub

    the executions ends and the form is automatically closed as you described. Now if the test_NewFrom is a macro executed from an excel Event (for instance a shape layed out on a sheet surface) it will be displayed as expected.