Naming Dynamically Created Controls on Imported Userforms

[CodeMonkey01]

Note: I am unable to reply to my own thread to bump it at work, therefore I have reposted (as it had zero replies) and deleted my original thread. EDIT: I thought I could delete my own post, but I cannot - my apologies to the forum and the moderators for misunderstanding that the edit post was a time-limited feature.

A colleague and I are building a template builder, which reads data from an XML, opens a new workbook, imports a number of pre-coded modules and userform(s), then dynamically adds buttons and labels to the userform, complete with event handlers. We've gotten 99% of it to work, but we cannot name our dynamically-created controls. For instance:

With wbProject.VBProject.VBComponents
        '--Load the list of nodes we want to loop through
        Set ufMain = .Item("ufMain")
        Set frmSummary = ufMain.Designer.Controls.Item("frmSummary")
 
        Set xmlNodeList = JOBXML.SelectNodes("/JobProcessingInstructions/LoanHeaders/LoanHeader")
        For Each xmlNode In xmlNodeList
            '--Retrieve the label value from the xml
            strLoanHeader = xmlNode.Attributes.getNamedItem("Value").nodeTypedValue
            Set lblHeading = frmSummary.Controls.Add("Forms.Label.1") ', "lbl" & strLoanHeader & "Heading")
            With lblHeading
                .Name = "lbl" & strLoanHeader & "Heading"
                .Top = 12 + (iRows * 18)

The compiler blows up on the .name line with this message:

Run-time error '40044':

Could not set the Name property. Could not complete the operation due to error 800a9c6c.

Google has failed me in regard to that error number (800a9c6c). Thus far, I have been unable to use ufMain.Controls(lblHeading.Name).Properties("Name") = TextString, either, as the compiler says the object doesn't support this property or method.

Does anyone know how I can set a name for my controls that are created this way? We've determined workarounds for this, but it's going to be a big pain and may not work in the future; this project builder is intended to be available to non-programmers who can edit an XML in the future.

I am working in Excel 2007.

Thank you in advance for any assistance.
<!-- / message -->

 

[CodeMonkey01]

This is just a code snippet - a few lines down, I'm writing code to imported code modules.

 

[mikerickson]

I can't add a control to a frame with the Designer object.
Apparently you can. (I'm on a Mac and not surprised with these different results).

But while investigating the matter, I found that Name is an optional argument of the .Controls.Add method.

Perhaps you could name your label at that time rather than later?

 

[Norie]

CodeMonkey

Have you checked the name you are trying for the label for all invalid characters and also the length of the string?

I created this code which is a bit messy, partly because of the multiple Replace functions used to elminate invalid characters.

I'm sure there's a much better way to do that - regular expressions perhpas.

Anyway the error I was getting whenever the name was invalid was exactly the same as the one you were getting.

Also got some ambiguous names, that's why I added the state suffix.

Option Explicit
 
Sub CreateFromXMLData()
Dim wbProject As Workbook
Dim vbProj As VBProject
Dim frm As VBComponent
Dim ctlFrame As MSForms.Frame
Dim ctlLbl As MSForms.Label
Dim obj As XMLHTTP
Dim xmlFormDoc As New MSXML2.DOMDocument
Dim ndCity As IXMLDOMNode
Dim lngTop As Long
Dim strCaption As String
Dim strName As String

    Set wbProject = ThisWorkbook
    
    Set vbProj = wbProject.VBProject
 
    Set frm = vbProj.VBComponents.Add(vbext_ct_MSForm)
 
    Set ctlFrame = frm.Designer.Controls.Add("Forms.Frame.1", "frmSummary")
 
    xmlFormDoc.Load "C:\USCities.xml"
 
    For Each ndCity In xmlFormDoc.SelectNodes("//Row")
 
        strCaption = ndCity.ChildNodes(1).Text

        strName = Replace(strCaption, " ", "_")
        strName = Replace(strName, ".", "")
        strName = Replace(strName, "-", "")
        strName = Replace(strName, "(", "")
        strName = Replace(strName, ")", "")
        
        strName = "lbl" & strName & UCase(Left(ndCity.ChildNodes(0).Text, 2))
 
        Set ctlLbl = ctlFrame.Controls.Add("Forms.Label.1", strName)
 
        ctlLbl.Caption = strCaption
 
        ctlLbl.Top = lngTop
        
        lngTop = lngTop + ctlLbl.Height
 
    Next ndCity
 
End Sub

 

[CodeMonkey01]

@mikerickson:

Good suggestion. You can see from the code, I've tried that one already - same error. I just commented it out after it failed.

Set lblHeading = frmSummary.Controls.Add("Forms.Label.1") ', "lbl" & strLoanHeader & "Heading")

@Norie:

Yes, the name would be valid: it would've been "lblNoteHeading", which is a valid control name.

The main difference I see between your code and mine is that you're adding a userform to your project, not importing it. I'm importing a userform that already has a few set controls on it, so mine would look like:

ThisWorkbook.VBProject.VBComponents.Import "C:\Test Files\MyForm.frm"
Set ufMain = ThisWorkbook.VBProject.VBComponents.Item("MyForm")

Try importing a userform with controls on it already and let me know if you're still able to have the same success. That's the sticking point on this project.

 

[Norie]

Well that might be where the type mismatch issue I mentioned previously was coming into it.

That had nothing to with the data type of the name but the objects you are working with eg forms, controls, imported modules etc.

If I get a chance I might check it out.

By the way, why not just create the userform completely from scratch?

Surely it wouldn't be that much more code than what you've got for importing the userform.

Just an idea.

PS I also had another crazy idea of not adding controls dynamically like this, but just adding them in the initialize event of the form or something like that.

You could still use XML for that.

 

[mikerickson]

It seems that programatically adding controls to a frame using the Design option is problematic. Would it be feasible to import a userform to your project that already has lots of controls in frames and then use the Designer to delete those that you don't need.