英文:
How to Assign a Macro to a Form Button, Using VBA
问题
我正尝试创建一个“导出按钮”，该按钮当前会创建一个包含特定工作表的新工作簿，将它们取消隐藏，并且其中一个工作表内包含一个按钮。此按钮名为“Export Email Button”。它使用一个始终会被复制/导出的工作表在当前工作簿上分配了一个宏，该宏名为“ExportEmail()”。一旦我复制这些工作表，选择按钮并更改“OnAction”后，似乎没有任何更新。
Sheets(mySheets).Copy
ActiveWorkbook.Worksheets("ExportEmail").Visible = xlSheetVisible
Dim ExternalLinks As Variant
Dim x As Long
'目的：断开外部链接
ExternalLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
For x = 1 To UBound(ExternalLinks)
ActiveWorkbook.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
Next x
'问题出在这里
ActiveWorkbook.Worksheets("ExportEmail").Buttons("Export Email Button").OnAction = "NewMacro"
'新按钮分配给"'Names Update Master.xlsm'!NewMacro"
ActiveWorkbook.SaveAs myFolder & NewName & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close savechanges:=False
**更新**
' '' & NewName & ".xlsm'' - 这应该返回"'June 2023.xlsm'"，但实际上返回"'C:/June 2023.xlsm'"
ActiveWorkbook.Worksheets("ExportEmail").Buttons("Export Email Button").OnAction = "''" & NewName & ".xlsm'!Sheet4.ExportEmail"
<details>
<summary>英文:</summary>
I am attempting to create a "Export Button" the button currently creates a new workbook with specific sheets from the existing one, unhides them and inside one of these sheets contains a button. This button is named `Export Email Button`. It is macro-assigned to the current workbook using a sheet that is always copied/exported, this macro is named `ExportEmail()` Once I copy these sheets, select the button and change the `OnAction`, nothing seems to be updating.
Sheets(mySheets).Copy
ActiveWorkbook.Worksheets("ExportEmail").Visible = xlSheetVisible
Dim ExternalLinks As Variant
Dim x As Long
'Purpose: To break external links
ExternalLinks = ActiveWorkbook.LinkSources(Type:=xlLinkTypeExcelLinks)
For x = 1 To UBound(ExternalLinks)
ActiveWorkbook.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
Next x
'Here the issue lays
ActiveWorkbook.Worksheets("ExportEmail").Buttons("Export Email Button").OnAction = "NewMacro"
'New button is assigned to "'Names Update Master.xlsm'!NewMacro"
ActiveWorkbook.SaveAs myFolder & NewName & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
ActiveWorkbook.Close savechanges:=False
**UPDATE**
' "'" & NewName & ".xlsm'" - This should return "'June 2023.xlsm'", but instead turns "'C:/June 2023.xlsm'"
ActiveWorkbook.Worksheets("ExportEmail").Buttons("Export Email Button").OnAction = "'" & NewName & ".xlsm'!Sheet4.ExportEmail"
</details>
# 答案1
**得分**: 2
如果您想将宏分配给新创建的工作簿，请将工作簿名称与宏以感叹号分隔添加到宏中。如果稍后以不同的名称保存文件，Excel将自动更新`OnAction`方法。
但是，我在这里看到另一个问题：当您通过复制工作表来创建新工作簿时，只有工作表模块中的宏会被复制。因此，如果您想复制`NewMacro`例程，您应该将其放入`ExportEmail`的工作表模块中。在这种情况下，您需要通过在工作表的`codeName`之前调用它（加上一个点）来调用它。请注意，工作表的代码名称不是工作表名称本身。
尝试以下操作（请注意，我已经为新工作簿和工作表使用了变量）。
```vbnet
ThisWorkbook.Sheets(mySheets).Copy
Dim newWB As Workbook, newWS As Worksheet
Set newWB = ActiveWorkbook
Set newWS = newWB.Sheets(1)
newWS.Visible = xlSheetVisible
Dim ExternalLinks As Variant
Dim x As Long
'目的：断开外部链接
ExternalLinks = newWB.LinkSources(Type:=xlLinkTypeExcelLinks)
For x = 1 To UBound(ExternalLinks)
newWB.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
Next x
'这应该会产生类似于“Book2!Sheet1.NewMacro”的结果
newWS.Buttons("Export Email Button").OnAction = newWB.Name & "!" & newWS.codeName & ".NewMacro"
newWB.SaveAs myFolder & NewName & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
newWB.Close savechanges:=False
希望这对您有所帮助。如果您有任何其他问题，请随时提出。
英文:
If you want to assign the macro to the new created workbook, add the workbook-name to the macro, separated with an exclamation mark. It doesn't matter if you later save the file with a different name, Excel will update the OnAction-method automatically.
However, I see another issue here: When you create a new workbook by copying a sheet, only the macro within the sheet module is copied. So if you want to copy the NewMacro-routine, you should put it into the sheet module of ExportEmail. In that case, you need to call it by preceding the codeName of the sheet (plus a dot). Note that the codename of a sheet is not the sheet name itself.
Try the following (note that I have used variables for the new workbook and -sheet).
ThisWorkbook.Sheets(mySheets).Copy
Dim newWB as Workbook, newWS as Worksheet
set newWB = ActiveWorkbook
set newWS = newWB.Sheets(1)
newWS.Visible = xlSheetVisible
Dim ExternalLinks As Variant
Dim x As Long
'Purpose: To break external links
ExternalLinks = newWB.LinkSources(Type:=xlLinkTypeExcelLinks)
For x = 1 To UBound(ExternalLinks)
NewWB.BreakLink Name:=ExternalLinks(x), Type:=xlLinkTypeExcelLinks
Next x
' This should result in something like "Book2!Sheet1.NewMacro"
newWs.Buttons("Export Email Button").OnAction = newWB.Name & "!" & newWS.codeName & ".NewMacro"
newWB.SaveAs myFolder & NewName & ".xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled
newWB.Close savechanges:=False
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论