Excel VBA: How to Copy a UserForm to Another Workbook

To copy an Excel VBA UserForm to another workbook, export the form from the source workbook’s VBA project and import its .frm file into the destination project. In Windows Excel, you can do this manually in the Visual Basic Editor or automate it with VBA, provided Excel allows programmatic access to VBA projects.

What copying a UserForm includes

A UserForm is a component of a workbook’s VBA project, not a worksheet or a cell range. Exporting it creates a .frm file containing the form definition and its code module. If the form contains resources such as images, the export may also create a companion .frx file. Keep the two files together: the form file may depend on the companion file to restore those resources.

Importing the form adds a new component to the destination workbook. It does not automatically copy every item the form uses. For example, a form’s code might call a procedure in a standard module, refer to a class module, read a named range, or rely on a worksheet in the source workbook. Those dependencies must be copied or adapted separately.

Copying only a UserForm also does not move workbook-level event code, such as code in ThisWorkbook, or code in worksheet modules. The form can appear in the destination project but still fail when it runs if it expects those other components or source workbook data.

Copy the form manually in the Visual Basic Editor

For a one-time transfer, the Visual Basic Editor’s export and import commands are usually the simplest approach. Work with both workbooks open in desktop Excel for Windows, and make sure you select the correct project in the Project Explorer.

Open the source and destination workbooks. In Excel, press Alt+F11 to open the Visual Basic Editor. On a Mac, use the Visual Basic Editor command from Excel’s Developer tools or menus; keyboard shortcuts can vary by keyboard and Excel version.
In Project Explorer, expand the source workbook’s project and find the form, such as UserForm1. Right-click it and choose Export File. Save the exported .frm file in a temporary folder. Keep any matching .frx file in that folder.
In Project Explorer, select the destination workbook’s project. Use File > Import File, select the exported .frm file, and import it. Check that the form now appears under the destination project.
Save the destination workbook in a format that retains VBA, such as .xlsm or .xlsb.

The exact menu presentation can differ slightly between Visual Basic Editor versions. On Excel for Mac, VBA is available in the desktop application, but Windows-specific Trust Center instructions and automated project-access code should not be assumed to work there. If the editor provides the export and import commands, manual transfer is generally the more portable option. Excel for the web does not provide the desktop VBA editor or run VBA macros.

Automate the transfer with VBA on Windows

A macro can export a form from the workbook containing the macro and import it into another workbook that is already open. The example below uses late-bound component objects, so it does not require adding a reference to the Visual Basic Extensibility library. It checks that the target workbook is different and that a component with the same name is not already present.

Before running any macro that changes a VBA project, save a separate copy of both workbooks. VBA changes cannot be undone with Ctrl+Z. Replace Target.xlsm and UserForm1 with the actual destination workbook name and form component name.

Option Explicit

Sub CopyUserFormToOpenWorkbook()
    Dim wbTarget As Workbook
    Dim sourceComponent As Object
    Dim importedComponent As Object
    Dim frmPath As String
    Dim frxPath As String
    Dim savedErrNumber As Long
    Dim savedErrDescription As String

    On Error GoTo Failed

    ' The source form is in the workbook containing this macro.
    Set wbTarget = Workbooks("Target.xlsm")
    If wbTarget Is ThisWorkbook Then
        Err.Raise vbObjectError + 1000, , "Choose a different destination workbook."
    End If

    Set sourceComponent = ThisWorkbook.VBProject.VBComponents("UserForm1")
    If sourceComponent.Type = 3 Then
        ' Type 3 is a UserForm component.
    Else
        Err.Raise vbObjectError + 1001, , "The named component is not a UserForm."
    End If

    If ComponentExists(wbTarget, sourceComponent.Name) Then
        Err.Raise vbObjectError + 1002, , "The destination already has a component with this name."
    End If

    frmPath = Environ$("TEMP") & Application.PathSeparator & "UserForm1.frm"
    frxPath = Environ$("TEMP") & Application.PathSeparator & "UserForm1.frx"

    ' Remove stale export files before creating a fresh export.
    On Error Resume Next
    Kill frmPath
    Kill frxPath
    On Error GoTo Failed

    sourceComponent.Export frmPath
    Set importedComponent = wbTarget.VBProject.VBComponents.Import(frmPath)

    ' The import is complete; remove the temporary export files.
    On Error Resume Next
    Kill frmPath
    Kill frxPath
    On Error GoTo 0

    MsgBox "The UserForm was imported into " & wbTarget.Name & "."
    Exit Sub

Failed:
    savedErrNumber = Err.Number
    savedErrDescription = Err.Description
    On Error Resume Next
    Kill frmPath
    Kill frxPath
    On Error GoTo 0
    MsgBox "Copy failed (" & CStr(savedErrNumber) & "): " & savedErrDescription
End Sub

Private Function ComponentExists(ByVal wb As Workbook, _
                                 ByVal componentName As String) As Boolean
    Dim component As Object

    For Each component In wb.VBProject.VBComponents
        If StrComp(component.Name, componentName, vbTextCompare) = 0 Then
            ComponentExists = True
            Exit Function
        End If
    Next component
End Function

Run this from a standard module in the source workbook after opening the destination workbook. The code expects the source form to be named UserForm1 and the destination file to be open as Target.xlsm. If your form uses an .frx resource file, the export places it beside the .frm file in the temporary folder; importing the .frm uses that companion file.

Allow access to the VBA project

On Windows, Excel blocks programmatic access to VBA projects unless the user enables it. In Excel for Microsoft 365, the setting is under File > Options > Trust Center > Trust Center Settings > Macro Settings. Enable Trust access to the VBA project object model if you understand and accept the implications. The option may be disabled or controlled by an organization’s administrator.

This setting is separate from the decision to enable macros in a workbook. It grants VBA code the ability to inspect or change VBA projects, so only use automation from a trusted workbook and only enable access when needed. A protected or locked VBA project can also prevent exporting or importing components. If the macro reports an automation or permission error, verify the setting, project protection, and workbook selection rather than repeatedly running it.

Check for dependencies and adjust the form

After importing, open the form in the destination project and review its code and controls. A form that displays correctly in the designer may still fail when a button is clicked or when the form initializes. Look for references to source-specific names and objects, including worksheet names, named ranges, workbook variables, custom classes, and helper procedures.

Standard or class modules: Export and import any required modules separately, or rewrite the form to call code already in the destination project.
Workbook and worksheet references: Replace assumptions such as ThisWorkbook.Worksheets("Data") if the destination uses a different sheet name or layout.
References to libraries: Check Tools > References in the Visual Basic Editor for missing references. A library available on one computer may not be installed or registered on another.
External resources: Confirm that image files, databases, or other files the form opens are available at the paths used by its code.

Also check how the form is shown. A standard module might contain a macro that runs UserForm1.Show; that caller is not part of the form export. If the destination needs a button, ribbon command, or workbook event to open the form, recreate or copy that separate entry point.

Duplicate names, workbook formats, and signatures

Each VBA project needs distinct component names. If the destination already contains a form or module with the same name, decide whether to keep the existing component, rename the incoming one, or replace the old one. Avoid deleting a component just to make an import succeed unless you have confirmed what uses it and saved a backup. The sample macro stops when it finds a matching name rather than attempting an automatic replacement.

The destination must be saved in a format that supports VBA. An .xlsx workbook cannot retain VBA project code when saved; use a macro-enabled workbook such as .xlsm, or a binary workbook such as .xlsb, when appropriate. Importing or editing VBA can also invalidate a digital signature on the project. If the workbook is signed or distributed under controlled processes, follow the owner’s signing and review procedures after making changes.

Common problems and practical alternatives

If manual import works but the macro does not, project access permission is a likely difference. If neither method works, check that the source file is not read-only, that the destination project is not locked, and that you selected the destination project before importing. If the form arrives but its image is missing, check that the matching .frx file accompanied the export.

For a single form, manual export and import often avoids the extra security configuration required for automation. If you need to distribute the form repeatedly, keep a clean template workbook or a reusable macro-enabled add-in. An add-in can make shared code available to multiple workbooks, but it is not the same as embedding a separate copy of the UserForm in each destination project; the right choice depends on whether each workbook must be self-contained.

Frequently Asked Questions

Can I copy a UserForm by dragging it between VBA projects?
Do not rely on drag-and-drop as the transfer method. Export the form from its source project and import the .frm file into the destination project, or use the VBA automation approach on Windows.

Does exporting a UserForm copy its code?
Yes. The exported form definition includes the UserForm’s code module. It does not automatically include separate standard modules, class modules, workbook events, worksheets, or other resources the code relies on.

Why does the macro say programmatic access is denied?
Excel may have Trust access to the VBA project object model turned off, or an administrator may control that setting. A locked VBA project can also block access. For a one-time transfer, use the Visual Basic Editor’s manual export and import commands if permitted.

Can I import the form into an XLSX workbook?
An XLSX file is not a VBA-capable format for retaining the imported form. Save the destination as .xlsm or .xlsb so its VBA project can be retained.

Will the copied form work on another computer?
It can, but the destination must have the required controls, libraries, referenced modules, data, and external files. Check references and paths, and test the form on the target computer before distributing the workbook.

Try it: the Column Letter to Number: aA is 27, XFD is 16,384. Converts both ways.

More in VBA & macros

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.