VBA TIPS
Copying UserForms programmatically

This program would copy Userforms in ThisWorkbook the new workbook programmatically.

Looping each VBComponent objects and if the type of the VBComponent is 3 (stands for an Userform), exports the form then import it from the new workbook. At last, This program would removes all exported files.

Project Window


Please note, if you are using Excel 2002(XP) and the later, to run this program, you need to tick Trust access to Visual Basic Project from Tools > Macro > Security... from main menu.

Trust access to Visual Basic Project


Place the following in the standard module.

Option Explicit

Sub UserFormCopyToNewWb()
'// UserForms of ThisWorkbook Copy to New Workbook.
    Dim objVbc As Object, wbNew As Workbook
    Set wbNew = Workbooks.Add
    For Each objVbc In ThisWorkbook.VBProject.VBComponents
        If objVbc.Type = 3 Then
            '// Export Form
            objVbc.Export Filename:=objVbc.Name
            '// Import Form to new workbook
            wbNew.VBProject.VBComponents.Import Filename:=objVbc.Name
            '// Kill Form Files
            Kill objVbc.Name
            Kill objVbc.Name & ".frx"
        End If
    Next
    Set wbNew = Nothing
    MsgBox "Done"
End Sub




| HOME |
Copyright © cellmasters.net - colo's junk room All Right Reserved
ABOUT
WORKS
THE CELL MASTERS
CONTACT
LINKS
Tips and Information about Microsoft Excel|Masaru Kaji aka Colo