CODEWITHSUNDEEP
vbaexcel
Jonny Oliver
Jonny Oliver

Reputation: 21

Error when programmatically adding text to existing userform

I want to add a text box to a pre-existing user form, and also change the height of the user form in VBA Excel 2010. I want the text box added, and height changed at design time but I need to make it with code. The following code is what I have so far.

Sub Practice()
Dim hght As Single
Dim NameUserForm As String
Dim MyUserForm As Object

'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

I get an error on the following line

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

And the error reads as:

Run-time error '-2147319767 (80028029)' Invalid forward reference, or reference to uncompiled type.

I have no idea what I'm doing wrong. I've also asked this same question on mrexcel.com, here's a link to that one http://www.mrexcel.com/forum/excel-questions/861582-use-visual-basic-applications-add-textbox-existing-userform.html

Upvotes: 2

Views: 1288

Answers (3)

TrickySituation
TrickySituation

Reputation: 378

I know this is an old thread, but I ran into this error as well. It's baffling, the error gives you no hits as to what is going on. In my scenario I'm programmatically adding a pivot chart. The weird thing is, I have known working code. I was working on an update in an unrelated area and my working code started showing this error. I couldn't tell what was going on, so I started stepping through connection objects. Eventually, I repro'ed this error when enumerating through ListObjects.

Sub ViewListObjectsWithQueryTables()
Dim sh, itm
For Each sh In ActiveWorkbook.Sheets
    Dim i
    i = 1
    Do While i <= sh.ListObjects.Count
        itm = sh.ListObjects.Item(i)
        If sh.ListObjects.Item(i).SourceType = xlSrcQuery Then
            itm = sh.ListObjects.Item(i)
        End If
        i = i + 1
    Loop
Next
End Sub

Put a line break at...

i = i +1

...and inspect values.

It turned out that when I hit a particular worksheet, and tried to read ListObjects.Count, I would get the error. It had nothing to do with my own code.

Resolution

  1. Delete the horked spreadsheet
  2. Recreate

This solved my problem. And possibly explains why @Jonny Oliver was able to fix, but didn't know why. The worksheet was in a horked state. Likely during his troubleshooting references behind the scenes may have changed. As for his code, there doesn't appear to be any reason why his original code would have resulted any different than his revised code.

Hope this helps.

Upvotes: 1

Jonny Oliver
Jonny Oliver

Reputation: 21

Well I got something to work. I'm not sure what I did that fixed it, but here it is for anyone else struggling with the same thing. (Note: I made some changes to variable names)

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

Upvotes: 0

ChipsLetten
ChipsLetten

Reputation: 2953

If you want to change the properties of a userform as part of the code that will Show the form, you are making changes at runtime. I think your code is mixing up making changes to the design of the saved form design with making changes to the current instance of the form.

The code below handles the userform as an object. The new textbox is added to the temporary instance of the form.

Sub Practice()
Dim hght As Single
Dim MyUserForm As testUserForm
Dim NewTextBox As Control

    Set MyUserForm = New testUserForm

    hght = MyUserForm.Height

    With MyUserForm

        .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
    MyUserForm.Show

    ' Do something with the form, user clicks Ok or close button
    Set MyUserForm = Nothing
End Sub

Upvotes: 0

Related Questions