Prevent form from closing after programmatically adding code

[daglivewire]

I'm generating a module programmatically when a command button is clicked on a modeless form. At the end of the code the form closes automatically without me explicitly unloading or hiding it. The form will stay open if I make it modal, but then the next modeless form that I open will do the same thing. I'm guessing this is happening to bring focus onto the new code in the VBA editor window at the first opportunity, but I want my program to keep running instead! Any ideas on how to get the form to stay open?

I'm using Excel 2010 Pro, VBA 7.0

Private Sub cmdGenerateModule_Click() 
    Dim mdlUserDefined As Object 
    Set mdlUserDefined = ActiveWorkbook.VBProject.VBComponents.Add(1) 
    mdlUserDefined.CodeModule.AddFromString "Public Sub Test_Code()" & Chr(13) & "End Sub" 
     
    UserForm1.Show ' this doesn't help
    Debug.Print "debug test" ' this line prints in the debugger, so the code executes before closing the form
End Sub

[daglivewire]

It seems that this is happening because the VBE will reset every time code is changed during run-time. As a workaround, I'm going to generate and use the new procedure in a temporary workbook. The downside is that the procedure doesn't have access to the code in the main workbook, but at least the program is able to keep running.

[SamT]

Why do you need create a module in the same workbook?

Better question: What exactly are you trying to accomplish that needs a new module?

[daglivewire]

The idea is to allow the user to type in some code that will be used in the program, without disrupting the flow of the application. My workaround appears to do the trick, although it is a little more roundabout than I initially thought it would be.

[Aflatoon]

I don't think it's ever a good idea to use code to write code into the same workbook but you could use an Application.OnTime call to reload the form if necessary. That would allow the code to finish, the project to reset and then the form to reappear. It would however re-run any initialize event code unless you use a flag variable to prevent it.

[SamT]

Design a hardcoded Modeless UserForm to get the info.
Show it with the get info button on the first form.
With its OK button, save the info and close the form

[daglivewire]

I don't think it's ever a good idea to use code to write code into the same workbook but you could use an Application.OnTime call to reload the form if necessary. That would allow the code to finish, the project to reset and then the form to reappear. It would however re-run any initialize event code unless you use a flag variable to prevent it.

Great suggestion. This works.

Design a hardcoded Modeless UserForm to get the info.
Show it with the get info button on the first form.
With its OK button, save the info and close the form.

This was my approach initially. It works so long as you are going from modal form to modal form. However, as soon as you open up another modeless form, the program will stop.

[SamT]

How many Forms are you showing at one time?

I tested it with two "ShowModal = False" forms and it worked ok for me.