如何用VBA在Excel新文件中创建宏按钮？保存后宏失效求助

问题根源 #

你当前的代码仅在原文件的工作表中创建了按钮，宏Macro1始终存储在原文件中。新文件的按钮只是指向原文件的宏引用，一旦原文件关闭或新文件重命名保存，按钮就无法找到宏的位置，导致失效。

解决方案 #

需要同时完成「创建带宏的新工作簿」「复制宏代码到新文件」「关联按钮与新文件内的宏」三个步骤，以下是完整的修正代码：

Sub CreateMacroEnabledFileWithButton()
    ' 创建新工作簿
    Dim newWB As Workbook
    Set newWB = Workbooks.Add
    
    ' 将原文件的宏代码复制到新工作簿的新模块中
    Dim sourceModule As Object, targetModule As Object
    Set sourceModule = ThisWorkbook.VBProject.VBComponents("Module1").CodeModule
    Set targetModule = newWB.VBProject.VBComponents.Add(vbext_ct_StdModule).CodeModule
    
    ' 复制Macro1的全部代码
    targetModule.AddFromString sourceModule.Lines(1, sourceModule.CountOfLines)
    
    ' 在新工作簿的第一张工作表添加按钮并关联宏
    With newWB.Sheets(1).Shapes.AddShape(msoShapeBevel, 85.25, 0, 120, 30)
        .Name = "Test_Shape"
        .OnAction = "'" & newWB.Name & "'!Macro1" ' 明确指定宏所在的新工作簿
        .TextFrame2.TextRange.Characters.Text = "执行Macro1" ' 给按钮添加显示文字
    End With
    
    ' 提示保存格式
    MsgBox "请将新工作簿保存为.xlsm格式，否则宏会被移除", vbInformation
End Sub

Sub Macro1()
    ' 示例宏代码
    Cells.Select
End Sub

关键说明 #

1. 代码复制逻辑：通过VBProject对象将原文件模块中的宏代码复制到新工作簿的新建模块中，确保宏随新文件转移
2. 宏关联方式：按钮的OnAction属性使用'新工作簿名'!宏名格式，明确指向新文件内部的宏，避免依赖原文件
3. 保存格式要求：新文件必须保存为.xlsm（启用宏的工作簿）格式，否则Excel会自动清除宏代码

注意事项 #

• 需提前开启Excel的「信任对VBA项目对象模型的访问」权限：

  文件 → 选项 → 信任中心 → 信任中心设置 → 宏设置 → 勾选「信任对VBA项目对象模型的访问」

• 如果原文件的宏存储在其他模块（非Module1），请修改代码中Module1为对应模块名

内容的提问来源于stack exchange，提问作者Iman FalizkaranYazdi