How do I add controls to userform via vba?

[MPW]

Hi All,

Using Office 2003 in XP

I can loop through controls but what is the syntax to add a control that I found while looping through a different userform?

    Dim VBProj As VBIDE.VBProject
    Dim VBComp As VBIDE.VBComponent
    Dim CodeMod As VBIDE.CodeModule
    
    Dim VBProj2 As VBIDE.VBProject
    Dim VBComp2 As VBIDE.VBComponent
    Dim CodeMod2 As VBIDE.CodeModule
    Dim LineNum As Long
    
    Set VBProj = oldwkbk.VBProject
    For Each VBComp In VBProj.VBComponents
        Set CodeMod = VBComp.CodeModule
        Set VBProj2 = newwkbk.VBProject
        Set VBComp2 = VBProj2.VBComponents.Add(VBComp.Type)
        VBComp2.Name = VBComp.Name
        For Each cCont In VBComp.Designer.Controls
              VBComp2.Designer.Controls.Add(cCont.Type)
             'I think the first part of this statement is right. 
             'However There is no "Type" allowed for the control property.

        Next
        Set VBProj2 = Nothing
        Set VBComp2 = Nothing
        Set CodeMod2 = Nothing
    Next VBComp

Could someone please point out the error of my ways. Within reason of course.

 

[mikerickson]

It looks like you are trying to copy the modules (inculding userforms) from one project to another.

I would recomend Exporting and then Importing the Userform.

To add a commnad button to a userform, the syntax would be

Dim newControl As Object

newControl = Userform1.Controls.Add("Forms.CommandButton.1")

But that doesn't set any of the properties (.Height,.Caption, etc) or provide any event code.

If you are transferring the Userform from one project to another, I recomend Export/Import.

 

[MPW]

Yeah, doing things the hard way. I thought I would do it this way to avoid bringing any baggage with it. The userforms are very old and were developed over a long learning curve (years) with a lot of deletion and re-writing along the way.
I would like to just start fresh with this and some other workbooks.

I am already importing all the code side and my next step is the control properties (height, width, color, etc.)

As for your input: How would I write that with the variable?

 

[MPW]

mikerickson,

Could you tell me what role the ".1" plays at end of:

newControl =  Userform1.Controls.Add("Forms.CommandButton.1")

Does this need to remain ".1" or could I substitute it for a variable?

Anyway, I have struggled through part of the answer.

Dim nC as String
For Each cCont In VBComp.Designer.Controls
   nC = "Forms." & TypeName(cCont) & ".1"
   VBComp2.Designer.Controls.Add(nC)

At this point I will rename the new control with the name of the original control, and then add the properties.

What I would like to avoid is renaming a control with an existing name which would be a bad thing. That is why I was asking about the ".1". If I can use a variable number, then I could bump it up higher then the number of controls. This would ensure that I would not overwrite an existing form or button, etc.

I want to thank you for pointing me in the right direction to cracking this.

 

[mikerickson]

The ".1" is needed.
If you look in the Object Browser, you'll see that Name is an optional argument of the Controls.Add method. To avoid naming mismatches, you could make use of the fact that the controls of the existing userform have unique names.

For each cCont in VBComp.Controls
    nc = "Forms." & Typename(cCont) " & ".1"
    VBComp2.Designer.Controls.Add nc, name:=cCont.Name
Next cCont

Also, you don't need to loop through the .Designer.Controls of the original UF. Or acesss it's Designer in any way. The .Designer object is only needed if you are going to make permanent changes in a UF, as you are doing to the new UF.

 

[MPW]

Perfect! That is just what I was missing.

Thanks.

 

[Norie]

Why not just drag the form from the original workbook to the new workbook in the Project Explorer in the VBE?

If you want to delete all the code just select it all and press Delete.

 

[MPW]

In a nutshell, from a wkbk I am browsing for the target wkbk. Creating a 3rd wkbk and reproducing all the properties (everything - cell values, formats, code, the works) of the target wkbk in the 3rd wkbk.

It has become more a quest than anything else, but I still think that it is cleaner as far as severing any ties to the original wkbk.

I'm a bit hard headed if you have not noticed yet. I am probably about 90% there.

The only thing I have not been able to figure out other than what mikerickson just helped me with, is how to renumber a sheet. I know how to rename one but to renumber Sheet2("Statistics") to Sheet5("Statistics") has boggled me pretty badly.

 

[mikerickson]

have you tried

Workbooks("some.xls").VBProject.VBComponents("Sheet1").Name = "newName"

(I'm at work and they don't "trust" anyone enough for me to test this, but if I recall correctly, re-code-naming a component is pretty easy.)

 

[MPW]

I can change the Sheet name from Sheets("Alpha") to Sheets("Beta") but in the VBE Project Explorer it would still be the same sheet number.
For instance:
Sheet1(Alpha) becomes Sheet1(Beta)
I am trying to do this.
Sheet1(Alpha) becomes Sheet5(Beta)