Issues with VBA's vbModeless for programmatically-created userforms: they close

[b4bis91]

EDIT: I have found a work-around, and no longer have the same question, but now a new question that I haven't been able to figure out. See my second post on here....

To start off, I apologize for this post being so lengthy and involved, but can assure you I have done exhaustive research into the matter and still have not reached a solution.

Just some background: I am new here, first post. I wrote my first line of code on VBA (and first line of code, period) about two months ago for a project. Now, I have been using it extensively. Whenever I look up something new, or do trouble-shooting, I have always found a a solution online... up until now.

My problem stems from creating a userform programmatically. Without boring you with the details of WHY I need to create a userform programmatically, hopefully you can take my word for it that I do have to create one programmatically.

Here's the scoop. I have a command button that creates a userform programmatically, that I am calling "ParentForm". One of the buttons on it will call up a pre-existing userform, one that I am calling "ChildForm". This "ChildForm", I would like to be shown as vbModeless. It is essential that users be able to scroll around the document while filling this form out.

I've had success in showing other forms as vbModeless when they are pre-existing and just simply called upon by a command button. But now that it is called upon by a command button within a programmatically created userform, it will no longer work. What happens? It loads up and immediately closes, all within a split second.

I have tried making the "ChildForm's" properties changed upon intialization "Properties("ShowModal") = False", but I get the error message "Can't show non-modal form when modal form is displayed."

The first form "ParentForm" (the modal one), isn't displayed at that point, though; it has been unloaded. Unless the error is saying once a userform is modal, it cannot be made modeless?

Anyway, here is the code I have. "Display" is just a command button (the current sub) that you push to run this code.

Private Sub Display_Click()

'A. Setting up "Parent Form"
Dim ParentForm As Object
Set ParentForm = ThisWorkbook.VBProject.VBComponents.Add(3)

With ParentForm
    .Properties("Width") = 200
    .Properties("Height") = 300
    '.Properties("ShowModal") = False
End With

'B. Creating command button to call up "ChildForm"
Dim ChildForm As MSForms.CommandButton
Set ChildForm = ParentForm.Designer.Controls.Add("forms.CommandButton.1")
With ChildForm
    .Caption = "Open"
    .Left = 20
    .Top = 240
End With

'C. Code Module
With ParentForm.CodeModule
    Line = .CountOfLines
    'Place button
    .InsertLines Line + 1, "Sub CommandButton1_Click()"
    .InsertLines Line + 2, "Unload Me"
    .InsertLines Line + 3, "ChildForm.Show 'vbModeless"
    .InsertLines Line + 4, "End Sub"
End With

'D. Opening form
VBA.UserForms.Add(ParentForm.Name).Show

'E, Deleting form
ThisWorkbook.VBProject.VBComponents.Remove ParentForm

End Sub

As you can see in section A. of the code, I have the line:
'.Properties("ShowModal") = False
ommented off, to avoid being run. If it is ran, it opens for a brief second then closes again. Granted, this is the "ParentForm", not the one I am looking to get modeless, but it would be a huge plus to get this one to become modeless.

As you can see in section C. of the code, I have the section of the line:
.InsertLines Line + 3, "ChildForm.Show 'vbModeless"
"vbModeless" commented off, again, to avoid being run. This time, it opens the second form up briefly, then closes both (although my code calls for the second one being closed after the first opens).

From the research I have done into the matter (and believe me, I have done some extensive internet digging, despite coming up short), one person said the solution would be:
ChildForm.Show vbModal
ChildForm.Hide
ChildForm.Show vbModeless

Although the OP (who had the same issue as me) found this to work, I in fact had not had success with this "solution".

I should have you know, that, to narrow things down, I am 100% certain this problem stems from the "ParentForm" being created programmatically. I recreated this situation with the "ParentForm" being created manually, where it calls up the "ChildForm" with a command button, and both were able to be made vbModeless without any problems at all (as long as the "ParentForm" was vbModeless too, otherwise I would get the "Can't show non-modal form when modal form is displayed." error).

So, I have looked over my code, and am not sure how I could change anything. It looks to me that the "modality" (if you will) of both forms has to be consistent, which is fine by me, I would prefer they both be modeless. I just can't figure out how to do this without them closing immediately.

I've written a lot, so I won't bore you with why the "ParentForm" has to be created programmatically, but take my word that it does. And, also, I do indeed have to use separate userforms as it isn't just one "ParentForm", and one "ChildForm". In fact, there are going to be 45 different "ChildForms" it calls up, depending on the situation in the worksheet.

I do feel that if I can get the "ParentForm" successfully modeless, then there shouldn't be any problems with the "ChildForm" being modeless, but who knows?

As far as spec's go, I am on Windows 7 using Excel 2007. Anyway, thank you very much in advance. I also have this community to thank for answering my previous questions (despite this being my first, my other questions have been answered to other posters). Thank you again.

[Logit]

.
I did some searching and found this (paste into a routine module) :

Option Explicit


Sub MakeForm()
    Dim TempForm As Object
    Dim NewButton 'As Msforms.CommandButton
    Dim Line As Integer


    Application.VBE.MainWindow.Visible = False


    Set TempForm = ThisWorkbook.VBProject. _
      VBComponents.Add(3) 'vbext_ct_MSForm
    With TempForm
        .Properties("Caption") = "Form"
        .Properties("Width") = 200
        .Properties("Height") = 100
    End With


    Set NewButton = TempForm.Designer.Controls _
      .Add("forms.CommandButton.1")
    With NewButton
        .Caption = "Click Me"
        .Left = 60
        .Top = 40
    End With
    With TempForm.CodeModule
        Line = .CountOfLines
        .InsertLines Line + 1, "Sub CommandButton1_Click()"
        .InsertLines Line + 2, "  MsgBox ""Hello!"""
        .InsertLines Line + 3, "  Unload Me"
        .InsertLines Line + 4, "End Sub"
    End With


    VBA.UserForms.Add(TempForm.Name).Show
    'ThisWorkbook.VBProject.VBComponents.Remove TempForm
End Sub


Sub shwfrm()
    UserForm1.Show
End Sub

http://www.java2s.com/Code/VBA-Excel...mmatically.htm

[b4bis91]

Thank you for that. I was not able to figure out how that would help with my vbModeless situation.

I would like to let everyone know that I have both good news and bad news. The good news is that I found a work-around. I found out that if you started with a pre-existing userform, a blank one, then used VBA to modify it programmatically, then you can use Show vbModeless and it works. The bad news is, I no longer understand how to use the module function.

     
     'C. Code Module
    With ParentForm.CodeModule 
        Line = .CountOfLines 
         'Place button
        .InsertLines Line + 1, "Sub CommandButton1_Click()" 
        .InsertLines Line + 2, "Unload Me" 
        .InsertLines Line + 3, "ChildForm.Show 'vbModeless" 
        .InsertLines Line + 4, "End Sub" 
    End With

In section C. of the code, it lets you use a code module
because the "ParentForm" was defined as an object.
If "ParentForm" is a pre-existing userform, it will
no longer work.

Maybe it is as simple as changing it into
ParentForm."Somethinghere".CodeModule

But maybe you can't export code modules into pre-existing
userforms?

I have tried just manually writing the code into it,
which seemed like you could do that, but it won't
recognize the command button anymore. It creates it, but when
you click it, nothing happens. For example, let me show
you how I edited the code this time.

Private Sub Display_Click()

'A. Adjusting properties for "Parent User Form"

ParentUserForm.Height = 200
ParentUserForm.Width = 300

'B. Creating command button to call up "Child User Form"
Dim CallChildUserFormButton As MSForms.CommandButton
Set CallChildUserFormButton = ParentUserForm.Controls.Add("forms.CommandButton.1")
With CallChildUserFormButton
    .Caption = "Call"
    .Left = 20
    .Top = 100
    .Name = "ChildUserFormButton"

End With

'C. Opening form

ParentUserForm.Show 'vbModeless

End Sub

As you can see, I got rid of the code module, and here
is the code I copied and pasted into the ParentUserForm
code section

Sub ChildUserFormButton_Click()

    Unload Me
    ChildUserForm.Show 'vbModeless

End Sub

Private Sub CommandButton1_Click()

    Unload Me
    ChildUserForm.Show 'vbModeless

End Sub

You would think the "ChildUserFormButton_Click" sub
would run the code, but it doesn't. Nothing happens when
you click the button. I can't manually add the button in, becase
in this case, the controls all have to be added programatically.

Thank you again.

[mdmackillop]

Starting from basic with two userforms, what do you need to change?

[mdmackillop]

This works in two parts
Edits userform and adds code. Userform must contain at least one character e.g. apostrophe (I'm sure there is a way around this)
Open form by a separate macro.

[mikerickson]

I think that the problem has to do with scope.

You have the variable newForm scoped at the procedure level. When Display_Click ends, it passes out of scope.
That's fine for modal user forms, since the .Show line doesn't finish exciting until the user dismisses the user form (i.e. while still in the calling procedure where newForm is in scope).

But with modeless forms, you want the newForm to persist after the calling sub ends.

If you declare newForm as a Public variable, outside of the calling routine, that should address that issue.


ALSO, I note that you are writing code to the newly created VBComponent's code module. That isn't needed and seems a bit heavy handed to me.
You could use class modules to receive the events of a newly created component.