Here’s another one of those features I’ve always been amazed that in over 30+ years Microsoft never saw fit to add to the Excel VBE, the ability to copy/paste, or if you prefer, duplicate a UserForm. Seems like the type of thing that developers could find useful, no?!
Manually Duplicating an Excel UserForm
Sure, you can duplicate an Excel UserForm manually:
- Rename the UserForm to the desired new UserForm name
- Export the UserForm
- Rename the UserForm back to its original name
- Import the exported UserFrom
- Activate the imported UserForm and start development
but after a while, if you do a lot of this type of work, it can become a time suck.
Duplicating an Excel Userform Using VBA
Today, I thought I’d share a small snippet of code I created long ago to automate this task.
'---------------------------------------------------------------------------------------
' Procedure : DuplicateUserForm
' Author : Daniel Pineault, CARDA Consultants Inc.
' Website : http://www.cardaconsultants.com
' Purpose : Duplicate an existing Userform
' Copyright : The following is release as Attribution-ShareAlike 4.0 International
' (CC BY-SA 4.0) - https://creativecommons.org/licenses/by-sa/4.0/
' Req'd Refs: None required
'
' Input Variables:
' ~~~~~~~~~~~~~~~~
' sUsrFrmName : (string) Name of the Userform to create a copy of
' sNewUsrFrmName : (string) Name to be given to the new copy
' bActivateNewUserFrm : (boolean) True => Activate/Set the focus of the VBE on the
' New Userformonce created
' False => Leave the VBE display unchanged
'
' Usage:
' ~~~~~~
' Call DuplicateUserForm "UserForm1", "ChartOptions"
' Returns -> True/False; True = Successfully duplication, False = Something went wrong
'
' Revision History:
' Rev Date(yyyy-mm-dd) Description
' **************************************************************************************
' 1 2021-09-25 Initial Public Release
'---------------------------------------------------------------------------------------
Public Function DuplicateUserForm(ByVal sUsrFrmName As String, _
ByVal sNewUsrFrmName As String, _
Optional bActivateNewUserFrm As Boolean = True) As Boolean
On Error GoTo Error_Handler
Dim sNewUsrFrmFileName As String
'Build a temporary file to export the current userform to
sNewUsrFrmFileName = Environ("Temp") & "\" & sNewUsrFrmName & ".frm"
'Rename the current userform
ThisWorkbook.VBProject.VBComponents(sUsrFrmName).Name = sNewUsrFrmName
'Export the userform to the temporary file
ThisWorkbook.VBProject.VBComponents(sNewUsrFrmName).Export sNewUsrFrmFileName
'Name the userform back to its original name
ThisWorkbook.VBProject.VBComponents(sNewUsrFrmName).Name = sUsrFrmName
'Import the file
ThisWorkbook.VBProject.VBComponents.Import sNewUsrFrmFileName
'Cleanup
'Delete the export file(s) if it exists (typically *.frm and *.frx)
If Len(Dir(sNewUsrFrmFileName)) > 0 Then Kill Replace(sNewUsrFrmFileName, ".frm", ".*")
'Set the focus on the new userform is requested
If bActivateNewUserFrm = True Then ThisWorkbook.VBProject.VBComponents(sNewUsrFrmName).Activate
DuplicateUserForm = True
Error_Handler_Exit:
On Error Resume Next
Exit Function
Error_Handler:
MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
"Error Number: " & Err.Number & vbCrLf & _
"Error Source: DuplicateUserForm" & vbCrLf & _
"Error Description: " & Err.Description & _
Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
, vbOKOnly + vbCritical, "An Error has Occured!"
Resume Error_Handler_Exit
End Function
It’s not a very complicated piece of code but it does save a couple click of the mouse and can even be used to automate processes. Hopefully it will help a few of you out there.