excel - 将宏分配给以编程方式添加的 vba 按钮

Question

我尝试以编程方式将 VBA 按钮添加到我的工作簿。
我可以创建它，但由于某种原因，我无法在其上链接宏。
我总是收到错误消息:

Cannot run the macro "xxx". the macro may not be available in this WB or all macros may be disabled

这是我的代码:

Private Sub Workbook_Open()
'Remove all old buttons of the worksheet
ActiveSheet.Buttons.Delete
'Restore Folder selector button
Set t = ActiveSheet.Range(Cells(2, 1), Cells(2, 1))
Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)
With btn
    .OnAction = "FolderSelector"
    .Caption = "Folder selector"
    .Name = "Folder Selector"
End With
End Sub

Sub FolderSelector()
 MsgBox Application.Caller
End Sub

有谁知道出了什么问题？

Answer 1

我建议使用以下语法以避免奇怪的命名问题:
在 ThisWorkbook

Option Explicit

Private Sub Workbook_Open()
    'Remove all old buttons of the worksheet
    ActiveSheet.Buttons.Delete
    'Restore Folder selector button
    Dim t As Range
    Set t = ActiveSheet.Cells(2, 1) 'note this is shorter and the same as .Range(Cells(2, 1), Cells(2, 1))

    Dim btn As Object
    Set btn = ActiveSheet.Buttons.Add(t.Left, t.Top, t.Width, t.Height)

    With btn
        .OnAction = "FolderSelectorButton_Click"
        .Caption = "Folder selector"
        .Name = "FolderSelectorButton"
    End With
End Sub

在一个模块 CommandButtonActions

Option Explicit

Public Sub FolderSelectorButton_Click()
   MsgBox Application.Caller
End Sub

您从按钮调用的过程必须在普通模块中，并且必须是 Public (这实际上是默认的)。