System error &H80004005 (-2147467259). Unspecified error

[Pookiemeister]

I'm getting the following error. When I click Ok, the next message box that appears states "Out of Memory". I tried closing all the open apps. Eliminated any unused public variables. What else should I do? This all started when I created this sub routine. I' not sure if this has anything to do with this error.

Sub CreateUserForm()
Dim myForm As Object
Dim NewFrame As MSForms.Frame
Dim NewButton As MSForms.CommandButton
Dim NewComboBox As MSForms.ComboBox
Dim NewListBox As MSForms.ListBox
Dim NewTextBox As MSForms.TextBox
Dim NewLabel As MSForms.Label
Dim NewOptionButton As MSForms.OptionButton
Dim NewCheckBox As MSForms.CheckBox
Dim X As Integer
Dim Line As Integer

'This is to stop screen flashing while creating form
Application.VBE.MainWindow.Visible = False

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

'Create the User Form
With myForm
    .Properties("Caption") = "Edit Email Addresses"
    .Properties("Width") = 300
    .Properties("Height") = 20
End With

'Create TextBox
Set NewTextBox = myForm.designer.Controls.Add("Forms.textbox.1")
With NewTextBox
    .Name = "txtbx_1"
    .Top = 10
    .Left = 10
    .Width = 150
    .Enabled = False
    .Font.Size = 8
    .Font.Name = "Tahoma"
    .BorderStyle = fmBorderStyleSingle
    .SpecialEffect = fmSpecialEffectSunken
End With

'Create CheckBox
Set NewCheckBox = myForm.designer.Controls.Add("Forms.checkbox.1")
With NewCheckBox
    .Name = "chkbx_1"
    .Top = 10
    .Left = 160
    .Caption = ""
    .Width = 10
End With

'Create CommandButton Create
Set NewButton = myForm.designer.Controls.Add("Forms.commandbutton.1")
With NewButton
    .Name = "cmd_1"
    .Caption = "clickMe"
    .Accelerator = "M"
    .Top = 10
    .Left = 200
    .Width = 66
    .Height = 20
    .Font.Size = 8
    .Font.Name = "Tahoma"
    .BackStyle = fmBackStyleOpaque
End With

'add code for textBox
myForm.codemodule.insertlines 1, "Private Sub UserForm_Initialize()"
myForm.codemodule.insertlines 2, "   If Me.chkbx_1.value = True Then "
myForm.codemodule.insertlines 3, "      Me.txtbx_1.Enabled = True "
myForm.codemodule.insertlines 4, "   End If "
myForm.codemodule.insertlines 5, "End Sub"

'Show the form
VBA.UserForms.Add(myForm.Name).Show

End Sub

 

[Pookiemeister]

When I comment out this subroutine, the same error message appears. Thanks

 

[jkpieterse]

Why are you creating a userform "on the fly"? It is hard-coded in that routine so you might as well manually create that form and open it directly instead of creating it. Does your current project contain many, many userforms by now?

 

[Pookiemeister]

I wanted to make a userform more dynamic. In one of the subroutines, it asks who they want to email this file to. Depending on the answer the userform will adjust the size of the form and equally space the not enabled textbox with that email address. So later on, if that person needs to be removed, they click on a checkbox to enable that textbox to make the email address changes. So in other words the userform will adjust the height to accommodate the number of textboxes.

This code has been tested numerous times and has created a few userforms at which time I removed these userforms from the Project -VBAProject window. I thought that these would also remove them from memory, but I guess not.

If there is a better way to achieve what I am wanting to do, please let me know. Thanks.

 

[jkpieterse]

You should add controls to a userform at runtime (temporarily), rather than adding them to the userform designer. The events you need for those controls can be handled by using a class module. This is exactly what I did in this (free) treeview "control": Excel, Access: An MSForms (all VBA) treeview

 

[Pookiemeister]

Is this a possible reason for this error message? If so, how can I fix it?

 

[jkpieterse]

By rewriting your code so it doesn't add an entire userform, but uses an existing userform to which you add the controls using the Me.Controls.Add command in a routine in the userform (like the Userform_Initialize event).
Here is the base code you need for that.

1. in the userform:

Option Explicit

Dim addedControls As Collection 'Keeps instances of added controls class modules in memory

Private Sub UserForm_Initialize()
    Dim i As Long
    Dim ctl As MSForms.TextBox
    Dim eventInstance As clsControlEvents
    Set addedControls = New Collection
    'Add 5 textboxes below each other, tie each to its own instance of the clsControlEvents class
    For i = 1 To 5
        Set ctl = Me.Controls.Add("Forms.TextBox.1")
        ctl.Top = 6 + i * 18
        ctl.Left = 6
        Set eventInstance = New clsControlEvents
        Set eventInstance.ctl = ctl
        addedControls.Add eventInstance
    Next
End Sub

Private Sub UserForm_Terminate()
    Set addedControls = Nothing
End Sub

2. A class module named clsControlEvents:

Option Explicit

Public WithEvents ctl As MSForms.TextBox

'The Change event of the ctl object. Use the drop-down at the top of the module to find other events.
Private Sub ctl_Change()
    MsgBox "You changed the value of control '" & ctl.Name & "' to: " & ctl.Value
End Sub

'Clean up when class goes out of scope
Private Sub Class_Terminate()
    Set ctl = Nothing
End Sub

Download a demo file from here: userform adding controls at runtime with class for events.xlsm