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.

 

[xenou]

There is information on changing sheet names in erik.van.geit's worksheet info thread:
http://www.mrexcel.com/forum/showthread.php?t=253546

Looks like you have to change a property value, which is always the codename you want to give the sheet prefixed by an underscore.

 

[xenou]

Note that a "quick and dirty" way to move your forms to a new workbook would be to round-trip the workbook through an html file format (save as html, then save as xlsm or xlsb).

I think the point is that html is a pure text file format so it pretty does what you're trying to do here (reduce the forms to text, then rebuild them as binary objects).

 

[mikerickson]

This shows how to change both the tab name and code name of a worksheet

Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets(1)

MsgBox "Tab name: " & ws.Name & vbCr & "code name: " & ws.CodeName

ws.Name = "new Tab Name"
MsgBox "Tab name: " & ws.Name & vbCr & "code name: " & ws.CodeName

ws.Parent.VBProject.VBComponents(ws.CodeName).Name = "newCodeName"
MsgBox "Tab name: " & ws.Name & vbCr & "code name: " & ws.CodeName

 

[mikerickson]

Also, here is a routine that will move a userform from one workbook to another.

Dim oldBook As Workbook, newBook As Workbook
Dim vbComp As VBComponent
Dim newVBComp As VBComponent
Dim tempFilePath As String

Set oldBook = ThisWorkbook
Set newBook = Workbooks("Workbook2.xlsm")

Set vbComp = oldBook.VBProject.VBComponents("UserForm1")

tempFilePath = Application.GetSaveAsFilename("temp")
If tempFilePath = "False" Then Exit Sub: Rem cancel

vbComp.Export tempFilePath

Set newVBComp = newBook.VBProject.VBComponents.Import(tempFilePath)

Kill tempFilePath

 

[xenou]

Note that a "quick and dirty" way to move your forms to a new workbook would be to round-trip the workbook through an html file format (save as html, then save as xlsm or xlsb).

Oops. I tested my suggestion out and userforms didn't survive the process. So much for that theory.

 

[MPW]

Thanks guys,

xenou,
I had heard about transferring using the html format. The more I looked at it and read up on it the more unreliable people said it was. I think it is possible to make it work but I didn't pursue it.

mikerickson,
Working with the code.name did the trick. I am still not that happy with the way I wrote it so I will have to play with that a bit more, but the principle is sound. I need to exclude Sheet1 since a new workbook already starts with a sheet1. I don't need to change the code.name, just the tab.

Anyway, thanks for the time.