vba 将用户表单复制到另一个工作簿
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/44479289/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Copy userform to another workbook
提问by Drum
I've had a look for an answer to this question but can't find anything that matches exactly what I'm looking for, if someone has the answer or can point me in the direction of a question which answers this, it would be much appreciated.
我一直在寻找这个问题的答案，但找不到任何与我正在寻找的完全匹配的东西，如果有人有答案或可以指出我回答这个问题的方向，那就是非常感激。
I've been talked with building a solution to enter data into a workbook and have successfully built it, however the workbook it need to be added to is a very active tool and is constantly being updated with new/changed data, so I have had to build it in a copy of the workbook and now need to add it into the active workbook. I know an easy way would be to simply grab the data out of the active workbook and add it into my version and then make my version the active book, however this would be a fairly large undertaking and I can only take the workbook offline for ~5 minutes so I figured the easier way would be to copy my code and userform into the active spreadsheet. The code is easy enough to copy and paste in, but I can't find a way to copy the userform (formatting and all) into the workbook, is there a way to export it and then import it into the live workbook?
我一直在谈论构建一个将数据输入工作簿的解决方案并成功构建它，但是它需要添加到的工作簿是一个非常活跃的工具，并且不断更新新的/更改的数据，所以我有在工作簿的副本中构建它，现在需要将其添加到活动工作簿中。我知道一个简单的方法是简单地从活动工作簿中获取数据并将其添加到我的版本中，然后使我的版本成为活动簿，但这将是一项相当大的任务，我只能将工作簿脱机 ~ 5 分钟，所以我认为更简单的方法是将我的代码和用户表单复制到活动电子表格中。代码很容易复制和粘贴，但我找不到将用户表单（格式化和所有）复制到工作簿中的方法，
I hope this is clear and easy to understand, please let me know if I need to clarify anything.
我希望这清楚易懂，如果我需要澄清任何事情，请告诉我。
回答by Patrick Lepelletier
i will show you how you can export & import a Userform in VBA , and then re-import it in an other workbook:
我将向您展示如何在 VBA 中导出和导入用户表单，然后将其重新导入到其他工作簿中：
Option Explicit
Public Function CopyUserForm(ByVal FormName$, Optional ByVal WB_Dest As Workbook) As Workbook 'copies sheets/Thisworkbook/Userforms/Modules/Classes to a new workbook
Dim Comp As VBComponent
Dim CompStr$
On Error Resume Next 'needed for testing if component already exists in destination WorkBook, and vbe minimizing
If WB_Dest Is Nothing Then Set WB_Dest = Application.Workbooks.add
For Each Comp In ThisWorkbook.VBProject.VBComponents
With Comp
If .Type = vbext_ct_MSForm Then '=3
If .Name = FormName Then
'// Export Form
CompStr = "C:\" & .Name ' & " " & Replace(Date, "/", "-") & ".frm"
.Export FileName:=CompStr 'this line fails if the destination Disk is protected, wich happened on my system disk :/
'// Import Form to new workbook
WB_Dest.VBProject.VBComponents.Import FileName:=CompStr
'// Kill temporary Form Files
Kill CompStr: Kill CompStr & ".frx"
Exit For
End If
End If
End With 'comp
Next Comp
Err.Clear: On Error GoTo 0
Set CopyComponentsModules = WB_Dest
Set Comp = Nothing
Set WB_Dest = Nothing
End Function
You can then call the function like this: CopyUserForm "UserForm1", for example.
然后，您可以像这样调用函数：CopyUserForm "UserForm1"例如。
回答by Alok
You can open code editor for both Excel sheets. Now drag and drop userform from one Excel to another Excel. Thanks
您可以打开两个 Excel 工作表的代码编辑器。现在将用户表单从一个 Excel 拖放到另一个 Excel。谢谢
