Error 91 when attempting to add control to previously opened form

Question

I've got two userforms - "PhaseHome" and "ModifyPhases".

I must go through the "PhaseHome" form in order to get to the "ModifyPhases" form. Once on the "ModifyPhases" form, I utilize a combo-box and button for the user to create a new & custom named userform that has a few controls. The code looks like this:

Please Note:

"Phasename" is the custom name the user entered in the earlier combo-box.

Sub New_form()

Dim Newphase As VBComponent
Dim ItemBox As MSForms.ComboBox
Dim AddItem As MSForms.CommandButton

Sheet1.Activate

'Creating the new form
Set Newphase = ActiveWorkbook.VBProject.VBComponents.Add(vbext_ct_MSForm)
    With Newphase
    .Properties("Height") = 250
    .Properties("Width") = 350
    .Properties("Caption") = Phasename
    .Name = Phasename
    End With


'Inserting the combobox into the dynamically created form
Set ItemBox = Newphase.Designer.Controls.Add("Forms.ComboBox.1")
With ItemBox
    .Name = Phasename & "Box"
    .Top = 60
    .Left = 12
    .Width = 140
    .Height = 80
    .Font.Size = 8
    .Font.Name = "Tahoma"
    .BorderStyle = fmBorderStyleOpaque
    .SpecialEffect = fmSpecialEffectSunken
End With

'Inserting buttons into the dynamically created form
Set AddItem = Newphase.Designer.Controls.Add("Forms.commandbutton.1")
With AddItem
    .Name = "cmd_1"
    .Caption = "Add Line Item"
    .Top = 5
    .Left = 200
    .Width = 110
    .Height = 35
    .Font.Size = 8
    .Font.Name = "Tahoma"
    .BackStyle = fmBackStyleOpaque
End With

With that done and the userform created; I now want to add a button to the "PhaseHome" form that allows the user to get to the form we just created.

Sheet1.Select
Range("D5").Value = Range("D5").Value + 45

'Add button to Phase Home Form
Dim homeform_button As MSForms.CommandButton
Dim ufObj As UserForm

Set ufObj = ActiveWorkbook.VBProject.VBComponents("Phasehome").Designer
With ufObj
    Set homeform_button = .Controls.Add("Forms.CommandButton.1")
    With homeform_button
        .Name = "cmd" + Phasename
        .Caption = Phasename
        .Top = Range("D5").Value
        .Left = 45
        .Width = 78
        .Height = 36
        .Font.Size = 8
        .Font.Name = "Tahoma"
        .BackStyle = fmBackStyleOpaque
    End With
End With

'Making sure we don't overwrite previously existing code when we insert this into PhaseHome
Dim linestart As Integer 

linestart = Range("D8").Value

ThisWorkbook.VBProject.VBComponents("PhaseHome").CodeModule.InsertLines linestart, "Private Sub cmd" & Phasename & "_Click()"
linestart = linestart + 1
ThisWorkbook.VBProject.VBComponents("PhaseHome").CodeModule.InsertLines linestart, "Unload Me"
linestart = linestart + 1
ThisWorkbook.VBProject.VBComponents("PhaseHome").CodeModule.InsertLines linestart, "Sheet2.Activate"
linestart = linestart + 1
ThisWorkbook.VBProject.VBComponents("PhaseHome").CodeModule.InsertLines linestart, "" & Phasename & ".Show"
linestart = linestart + 1
ThisWorkbook.VBProject.VBComponents("PhaseHome").CodeModule.InsertLines linestart, "End Sub"
linestart = linestart + 1

Range("D8").Value = linestart

Now the good news is that this code works!.... As long as I run it from the "ModifyPhases" form directly. Once, for the very first time in a session, that I open and close the "PhaseHome" form I start receiving an error 91 (Object variable or With block Variable not set) that points to the

Set homeform_button = .Controls.Add("Forms.CommandButton.1")

line everytime I attempt to run the macro again.

Things I've tried:

I've made sure that the "PhaseHome" form is unloaded. The button that goes between the userforms always includes an Unload Me, i've also tried unloading "PhaseHome" directly within the macro itself, and also used variables tied to "PhaseHome"'s terminate and Initialize functions to ensure it is unloaded without referencing it directly.
After noticing that refreshing the workbook fixed the issue, I discovered some code online (From a source I regretfully forget) that closes and reopens the workbook each time the "ModifyPhases" form is launched which fixes the issue.

Sub CloseMe() Application.OnTime Now + TimeValue("00:00:02"), "OpenMe" ThisWorkbook.Close True End Sub

Sub OpenMe() ModifyPhases.Show End Sub

I don't know why the code tags aren't working right here.

This works but... causes corruption in the workbook and also seems rather unnecessary. Do you fellows have any theories on why this could be occurring? Thank you!

-Mano

Why are you attempting to add to the .Designer? I think you should be able to add forms to the control directly, and I think that would (usually) be preferable. — David Zemens
Also note some differences you are using both ActiveWorkbook and ThisWorkbook, when the 91 error raises, are you sure that ThisWorkbook is the ActiveWorkbook? If not, then an object error is probably expected. — David Zemens
Ultimately I think (and you can clarify, of course) that you probably do not need to use VBE in this case. Do you need to maintain a collection of dynamically added controls? It looks like it would be much easier for you to create the "custom" form, and then modify its properties at run-time, rather than attempting to build a GUI at runtime, dynamically. — David Zemens
1. Changing ThisWorkbook to Activeworkbook did not seem to make a difference. 2. How is it possible to add a control to an existing form without using the Designer? I don't think I've found an example that doesn't involve using the designer. 3. I do not need a collection of the controls you are correct... I modify the properties of the "custom" form at runtime but I cannot add the button to the other (PhaseHOme) form at runtime as it stands. — manofone
Ah, I apologize I misunderstood your third comment. I DO need to maintain a collection of the controls. The macro may be run many times and each time it is run I need a new custom button for each custom form added to "PhaseHome". If the user later deletes that Phase then the button is just hidden on "Phasehome" but that part seems to work. — manofone

David Zemens David Zemens · Accepted Answer · 2016-05-16T21:34:00

Make no mistake about it: Modifying controls at run-time, and especially adding forms at run-time, is a high-level VBA exercise, and I think that it simply is beyond what you're currently capable of. (Please don't take this the wrong way, I don't mean to condescend)

I need a new custom button for each custom form added to "PhaseHome".

I'm gathering that this is the hang-up, and also source of error. So...

Is there an easier way??

Let's ditch that idea altogether!

Use a different control type that is more amenable to modifications at run-time. Buttons are tricky because they require each their own _Click event handler. Instead of adding buttons for each phase, just add a new item to a ComboBox control, and leverage its _Change (or some other) event as a method of user-input.

IOW, instead of expecting the user to press a button that displays a form, just let them select the form from a ComboBox!

Then, invoking that ComboBox's _Change event, refer back to the dict/collection of properties, and display the "Phaseform" object where you can modify it's controls at runtime, as needed.

Now you have a relatively generic form that will be used for any possible phase.

The List property of the Combobox is itself dynamic, and has a _Change event handler which you can use!

Private Sub cbox_PhaseNames_Change()

    MsgBox Me.Value  'Show the value which is selected, for debugging

    'Modify the Newphase userform. There is only one form, and its properties
    ' will be modified based on the selection from the cBox_Phasenames control

   Newphase.Caption = Me.Value

   'If you need to change other controls, you can probably do that here, too

End Sub

Example

I created some crude example (download from Google Drive if you'd like), using only two user forms. Phasehome implements described above, and the Phaseform can be modified (e.g., it's Caption) based on the selection in the ComboBox on PhaseHome.

NOTE: You're using 3 forms at least, please make note that I'm only using 2, what my example does for Pagehome really is probably more applicable to your ModifyPhases form, so take note of that and modify accordingly.

This would be how I set up the code for Phasehome:

Option Explicit
Private Sub cbox_Phasenames_Change()
    Dim val$, bFound As Boolean
    Dim i As Long
    
    'crude validation:
    val = Me.cbox_Phasenames.Value
    For i = 0 To Me.cbox_Phasenames.ListCount - 1
        If val = Me.cbox_Phasenames.List(i) Then
            bFound = True
            Exit For
        End If
    Next
    
    If Not bFound Then Exit Sub 'Avoid errors
    
    'Modify the PhaseForm:
    With phaseForm
        .Caption = val
        .Show
    End With
    
End Sub

Private Sub CommandButton1_Click()
   
    'Very simple example, allows duplicates, which you probably want to avoid
    Me.cbox_Phasenames.AddItem Me.TextBox1.Value

End Sub

And here is the code for Phaseform, I've commented a few items, but using the Initialize event to assign the properties you've set up:

Option Explicit

Private Sub UserForm_Initialize()
    Me.Height = 250
    Me.Width = 350
    'Me.Caption = ""   '## This is set in the calling procedure
    With Me.ComboBox1
        .Top = 60
        .Left = 12
        .Width = 140
        .Height = 80
        .Font.Size = 8
        .Font.Name = "Tahoma"
        .BackStyle = fmBackStyleOpaque
    End With
    With Me.CommandButton1
        '### There is no need to assign a dynamic Name property to this control
        '.Name = "cmd" + Phasename
        .Caption = ""
        .Top = Range("D5").Value
        .Left = 45
        .Width = 78
        .Height = 36
        .Font.Size = 8
        .Font.Name = "Tahoma"
        .BackStyle = fmBackStyleOpaque
    End With
    
End Sub

Private Sub ComboBox1_Change()
    MsgBox "Does something..."
End Sub

Note: If there are additional properties that you need to persist during runtime can be done via Static variables and using possibly Collection or Dictionary object to assist with organizing what's needed. Keep the "list" of phases in an Array, and keep their relevant properties in a dict/collection, etc. If absolutely necessary you can store some things in a hidden worksheet, or in a Name in the workbook, or in CustomXMLPart, etc. -- there's lots of ways you can conceivably persist metadata beyond the user session, so that the changes will be available tomorrow, next week, etc.

Error 91 when attempting to add control to previously opened form

2 Answers

Example