Use VBA to Add textbox to existing userform

Jonnyoforem

New Member
Joined
May 22, 2015
Messages
32
I'm trying to create a macro that will increase the height of a pre-existing user form, then add a textbox. I want this done at design-time so it will be permanent after running. I can't do it graphically because other users will be deciding when these text boxes need to be added. Here's the code I have so far.

Code:
Sub Practice()
    Dim hght As Single
    Dim NameUserForm As String
    Dim MyUserForm As Object
    Dim NewTextBox As MSForms.TextBox
    
    'Name of userform
    NameUserForm = "test"
    
    Set MyUserForm = ThisWorkbook.VBProject _
    .VBComponents(NameUserForm)
    
    hght = MyUserForm.Properties("Height")
    
    With MyUserForm
    
        .Properties("Height") = hght + 25
        
    End With
        
    Set NewTextBox = MyUserForm.Controls.Add("Forms.TextBox.1")
    
    With NewTextBox
        .TextAlign = fmTextAlignCenter
        .Width = 66
        .Height = 18
        .Left = 40
        .Top = hght
    End With
            
    test.Show
    
End Sub

The existing user form is called test and when I debug my code I get an error on the following line

Code:
Set NewTextBox = MyUserForm.Designer.Controls.Add("Forms.TextBox.1")

The error is the following: Run-time error '-2147319767 (80028029)': Invalid forward reference, or reference to uncompiled type.

Thanks in advance for any help. And if I could also get an explanation as to why my code is failing that'd be great. I'd really like to tame the VBA beast.
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
The user form will be manipulated by other people with little to no excel experience, and it may or may not be changed on many different occasions. I will not be around every time the user form needs to be changed. I'd like to create a button that runs a macro that will add the text box using code. Thanks anyway for the suggestion. P.S. I posed this same question on stackoverflow.com. Here's the link to that post https://stackoverflow.com/questions...rammatically-adding-text-to-existing-userform
 
Upvote 0
Your first code doesn't actually include the line you said was in error. Is the form loaded when you're trying to add the control using the Designer? If so, that would explain the problem.
 
Upvote 0
Hey Rory thanks for responding. Just to clarify, I had been fiddling with my code and had removed the .Designer, then accidentally posted that as the actual code. The code should look like this

Code:
Sub Practice()
    Dim hght As Single
    Dim NameUserForm As String
    Dim MyUserForm As Object
    Dim NewTextBox As Control
    
    'Name of userform
    NameUserForm = "test"
    
    Set MyUserForm = ThisWorkbook.VBProject _
    .VBComponents(NameUserForm)
    
    hght = MyUserForm.Properties("Height")
    
    With MyUserForm
    
        .Properties("Height") = hght + 25
        
    End With
    
    Set NewTextBox = MyUserForm.Designer.Controls.Add("Forms.TextBox.1")
    
        With NewTextBox
            .TextAlign = fmTextAlignCenter
            .Width = 66
            .Height = 18
            .Left = 40
            .Top = hght
        End With
        
    test.Show
    
End Sub

To ensure the form was loaded I threw in the following line right after declaring my variables.

Code:
Load test

Now I'm getting a different error message on that same line of code

Code:
 Set NewTextBox = MyUserForm.Designer.Controls.Add("Forms.TextBox.1")

The new error message is: Run-Time error '91' Object variable or With block variable not set.
 
Upvote 0
My point was that the form must NOT be loaded.
 
Upvote 0
Sorry I misinterpreted your response. No the form is not loaded. Just to recap, when it's not loaded I get the error message: Run-time error '-2147319767 (80028029)': Invalid forward reference, or reference to uncompiled type. When it is loaded I get the error message: Run-Time error '91' Object variable or With block variable not set.
 
Upvote 0
Well I got something that does what I want. Here's the code I used.

Code:
Sub DesignTimeTxtBox()


    Dim txtBox As Variant
    Dim NameUserForm As String
    Dim hght As Single
    
    NameUserForm = "test"
    
    Set MyUserForm = ThisWorkbook.VBProject _
        .VBComponents(NameUserForm)
        
    hght = MyUserForm.Properties("Height") + 25
    MyUserForm.Properties("Height") = hght
    
    Set txtBox = ThisWorkbook.VBProject.VBComponents(NameUserForm).Designer.Controls.Add("Forms.TextBox.1")
    With txtBox
        .Width = 66
        .Top = 133
        .Left = 42
    End With
    
End Sub

I'm still not sure why I was getting that error message. Just glad it's working.
 
Upvote 0
I've only ever seen the error you describe occur when the form is loaded?

Are you sure it isn't loaded but not displayed?

Here's some code that's meant to add a command button to a page of a multipage.
Code:
Sub ADDcmdButton()
Dim ufObj As Object
Dim newButton As Object

    Set ufObj = ThisWorkbook.VBProject.VBComponents("UserForm1").designer

    UserForm1.MultiPage1.Value = 0

    With ufObj
        Set newButton = .MultiPage1.Pages("Page1").Controls.Add("Forms.CommandButton.1", "NewCommand", True)
        With newButton
            .Top = 20
            .Left = 10
            .Caption = "Test Botton"
            .AutoSize = True
        End With
    End With

End Sub
It doesn't work and generates the error you describe.

The reason it doesn't work is this line,
Code:
UserForm1.MultiPage1.Value = 0

which actually loads the userform.

Once that line is removed the code works.
 
Upvote 0
Thanks for the info Norie. When I was debugging I added the line
Code:
Unload test
Right after declaring my variables and that gave me the invalid forward reference error. When I would load the form using the line
Code:
Load test
right after declaring variables, that's when I got the Object variable or With block variable not set error. Could another line of code been loading the form after I unloaded it? I'm still not terribly savvy with vba.
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,956
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top