批量顺序执行同名按钮宏的Excel VBA实现需求

我有一个多标签页Excel工作簿，其中Summary标签页每行数据对应一个绑定了SubmitData宏的按钮。点击按钮时，宏会更新其他工作表，并复制该行及其他已更新工作表的数据。现在需要创建一个宏，无需逐个编码就能按顺序触发所有按钮（按钮数量超100个且可能变动）。

现有代码 #

绑定到按钮的SubmitData宏 #

Sub SubmitData()
    Dim inputSheet As Worksheet
    Dim outputSheet As Worksheet
    Dim DetailedSheet As Worksheet
    Dim lastRow As Long
    Dim columnRange_status As Range
    
    Set inputSheet = Worksheets("Summary")
    Set DetailedSheet = Worksheets("DetailedTab")
    Set outputSheet = Worksheets("OutputFile")

    Set columnRange_status = inputSheet.Range("SubmitButton_Status")
       
    ' Condition before submitting
    If inputSheet.Range("W" & ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row).Value = "Pass" Then

    lastRow = outputSheet.Cells(outputSheet.Rows.Count, 1).End(xlUp).Row + 1

    
    DetailedSheet.Range("C2").Value = inputSheet.Range("A" & ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row).Value

    With inputSheet
        'Date
        outputSheet.Cells(lastRow, 1).Value = Date
        'Enligsh Short Name
        outputSheet.Cells(lastRow, 2).Value = .Range("A" & ActiveSheet.Shapes(Application.Caller).TopLeftCell.Row).Value
            End With
            
        With DetailedSheet
  
    'Quarterly Data used by Model - Quarter minus 1
        outputSheet.Cells(lastRow, 291).Value = .Range("AC9").Value

        End With
        
    Else
    
        MsgBox "Cannot submit. Condition is not met yet."
    End If
    

End Sub

尝试的批量触发宏MasterBtn_Click #

Sub MasterBtn_Click()
For Each OLEObject In Worksheets(1).OLEObjects
    If TypeName(OLEObject.Object) = "Submit" Then OLEObject.Object = True
Next
End Sub

解决方案 #

原MasterBtn_Click宏存在类型判断错误，且未按行顺序触发按钮。以下是修正后的两个版本，分别适配ActiveX控件和表单控件：

适配ActiveX命令按钮的版本 #

Sub MasterBtn_Click()
    Dim ws As Worksheet
    Dim oleObj As OLEObject
    Dim btnList As Collection
    Dim btnItem As Variant
    
    Set btnList = New Collection
    Set ws = ThisWorkbook.Worksheets("Summary")
    
    ' 筛选绑定SubmitData宏的ActiveX命令按钮，按行号存入集合
    For Each oleObj In ws.OLEObjects
        If TypeName(oleObj.Object) = "CommandButton" And oleObj.Object.OnAction = "SubmitData" Then
            btnList.Add oleObj, Key:=CStr(oleObj.TopLeftCell.Row)
        End If
    Next oleObj
    
    ' 按行号顺序触发宏
    For Each btnItem In btnList
        Application.Run btnItem.Object.OnAction
    Next btnItem
End Sub

适配表单控件按钮的版本 #

如果你的按钮是Excel原生表单控件而非ActiveX控件，使用以下代码：

Sub MasterBtn_Click_FormControls()
    Dim ws As Worksheet
    Dim shp As Shape
    Dim btnList As Collection
    Dim btnItem As Variant
    
    Set btnList = New Collection
    Set ws = ThisWorkbook.Worksheets("Summary")
    
    ' 筛选绑定SubmitData宏的表单按钮，按行号存入集合
    For Each shp In ws.Shapes
        If shp.Type = msoFormControl And shp.FormControlType = xlButtonControl Then
            If shp.OnAction = "SubmitData" Then
                btnList.Add shp, Key:=CStr(shp.TopLeftCell.Row)
            End If
        End If
    Next shp
    
    ' 按行号顺序触发宏
    For Each btnItem In btnList
        Application.Run btnItem.OnAction
    Next btnItem
End Sub

关键说明 #

• 精准筛选：通过控件类型+绑定宏名称双重判断，避免误触发无关控件
• 按行排序：利用集合键存储按钮所在行号，遍历自动按行号升序执行，保证数据处理顺序正确
• 稳定触发：使用Application.Run直接调用宏，比模拟按钮点击更可靠，兼容宏的原有逻辑

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