如何为工作表命令按钮调用的Sub传递ActiveSheet及按钮对象参数？

这个问题我之前也碰到过——Excel的「分配宏」功能确实不支持直接给指定的宏传参，因为它要求绑定的宏必须是无参数的过程，所以你之前直接写SubToBeCalledByButton(activesheet, ...)的写法才会报错。不过我们可以用一个「代理过程」来绕开这个限制，完美实现你要的需求：

具体实现步骤 #

1. 编写一个无参的代理过程 #

这个过程的作用是获取点击的按钮对象和它所在的工作表，再把这两个参数传递给你的目标DetailButton过程：

Public Sub ButtonProxy()
    Dim targetBut As Shape
    Dim targetWs As Worksheet
    
    ' 通过Application.Caller获取被点击按钮的名称，进而拿到按钮对象
    Set targetBut = ActiveSheet.Shapes(Application.Caller)
    ' 从按钮对象的Parent属性获取所在工作表
    Set targetWs = targetBut.Parent
    
    ' 调用你的目标过程，传递参数
    DetailButton targetWs, targetBut
End Sub

2. 保留你原有的目标过程 #

不需要修改你已经写好的DetailButton，它可以正常接收参数：

Public Sub DetailButton(ws As Excel.Worksheet, but As Object)
    Debug.Print "ws:" & vbTab & ws.Name
    Debug.Print "but:" & vbTab & but.Name
    Debug.Print "cal:" & vbTab & Application.Caller
End Sub

3. 给按钮分配代理过程 #

右键点击你的Button_1，选择「分配宏」，在弹出的列表里选中ButtonProxy，点击确定即可。

为什么这个方法可行？ #

• Application.Caller在按钮被点击时，会返回该按钮的名称（字符串），我们用这个名称就能精准定位到被点击的按钮Shape对象。
• 通过Shape对象的Parent属性，就能直接获取到它所在的工作表对象，不用硬编码工作表名称，灵活性更高。
• 代理过程是无参的，符合Excel「分配宏」的要求，完美绕开了直接传参的限制。

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