跨文档宏调用含MsgBox的函数，如何默认自动确认OK？

Got it, since you can't modify the original macro that pops up a MsgBox when called cross-workbook, here are two practical ways to auto-dismiss that OK prompt without touching the source code:

方法1：使用SendKeys（简单快速） #

SendKeys模拟键盘输入，我们可以在调用目标宏前设置一个短暂延迟，然后发送回车键来自动点击MsgBox的OK按钮。

代码示例 #

Sub CallRemoteMacroWithAutoOK()
    ' 设置1秒延迟，确保MsgBox有足够时间弹出（可根据实际情况调整）
    Application.Wait Now + TimeValue("00:00:01")
    
    ' 发送回车键，模拟点击OK
    SendKeys "{ENTER}", True
    
    ' 调用跨文件的目标宏
    Application.Run("'文件名'!待调用函数")
End Sub

注意事项 #

• SendKeys依赖Excel窗口处于活动状态，运行这个宏时不要切换到其他程序，否则按键会发送到错误的窗口。
• 延迟时间可以根据MsgBox弹出的速度调整，如果弹窗较慢，适当延长等待时间。

方法2：使用Windows API（更稳定可靠） #

如果SendKeys的稳定性无法满足需求，Windows API方法可以精准定位MsgBox窗口并发送点击指令，无需依赖窗口焦点，适合复杂场景。

步骤1：声明API函数 #

在VBA模块的顶部（所有子过程之外）添加以下API声明：

#If VBA7 Then
    Declare PtrSafe Function FindWindow Lib "user32.dll" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As LongPtr
    Declare PtrSafe Function FindWindowEx Lib "user32.dll" Alias "FindWindowExA" (ByVal hWnd1 As LongPtr, ByVal hWnd2 As LongPtr, ByVal lpsz1 As String, ByVal lpsz2 As String) As LongPtr
    Declare PtrSafe Function SendMessage Lib "user32.dll" Alias "SendMessageA" (ByVal hwnd As LongPtr, ByVal wMsg As Long, ByVal wParam As LongPtr, ByVal lParam As String) As LongPtr
#Else
    Declare Function FindWindow Lib "user32.dll" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
    Declare Function FindWindowEx Lib "user32.dll" Alias "FindWindowExA" (ByVal hWnd1 As Long, ByVal hWnd2 As Long, ByVal lpsz1 As String, ByVal lpsz2 As String) As Long
    Declare Function SendMessage Lib "user32.dll" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As String) As Long
#End If

Private Const BM_CLICK = &HF5

步骤2：编写自动关闭逻辑和调用宏 #

Sub AutoDismissMsgBox()
    Dim hwnd As Variant
    Dim btnHwnd As Variant
    Dim startTime As Date
    
    ' 记录开始时间，防止无限循环（超时5秒自动退出）
    startTime = Now
    
    Do
        ' 查找MsgBox窗口（类名为#32770，可指定标题精准匹配，留空则匹配所有MsgBox）
        #If VBA7 Then
            hwnd = FindWindow("#32770", vbNullString)
        #Else
            hwnd = FindWindow("#32770", vbNullString)
        #End If
        
        ' 如果找到窗口，定位OK按钮并发送点击指令
        If hwnd <> 0 Then
            btnHwnd = FindWindowEx(hwnd, 0, "Button", "OK")
            If btnHwnd <> 0 Then
                SendMessage btnHwnd, BM_CLICK, 0, vbNullString
                Exit Do
            End If
        End If
        
        ' 超时退出
        If Now > startTime + TimeValue("00:00:05") Then
            Exit Do
        End If
        
        ' 短暂暂停，降低CPU占用
        DoEvents
    Loop
End Sub

Sub CallRemoteMacro()
    ' 提前0.5秒启动自动关闭逻辑，确保MsgBox弹出时能被捕获
    Application.OnTime Now + TimeValue("00:00:00.5"), "AutoDismissMsgBox"
    
    ' 调用跨文件的目标宏
    Application.Run("'文件名'!待调用函数")
End Sub

注意事项 #

• 如果知道MsgBox的具体标题，可以把vbNullString替换成标题文本，比如"提示"，这样只会关闭指定标题的弹窗，避免误操作其他窗口。
• 这个方法不依赖Excel窗口焦点，运行过程中可以切换到其他程序，稳定性更高。

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