以编程方式关闭MsgBox [英] Programmatically dismiss a MsgBox

问题描述

我在Excel文件中有一个主宏，'文件A'打开另一个Excel文件'文件B'。打开时，加载项将数据导入文件B。加载项完成导入后，我想关闭'文件B'，我正在寻找最好的方法。

I have a master macro in an Excel file, 'file A' that opens another Excel file, 'file B'. On open, an add-in imports data into 'file B'. I would like to close 'file B' once the add-in is finished importing, and I'm looking for the best way to do that.

我写了打开文件B（自动触发加载项）并关闭文件的代码，但是当加载项完成后，它会打开一个MsgBox来通知用户。我试图完全自动化一个内部流程，所以以编程方式解除MsgBox是理想的。

I've written the code to open 'file B' (which triggers the add-in automatically) and to close the file, but when the add-in is finished, it opens a MsgBox to notify the user. I'm trying to completely automate an internal process, so dismissing the MsgBox programmatically would be ideal.

是否可以通过VBA关闭MsgBox？我知道我可以在VBA中创建定时的MsgBox，但是我没有创建这个MsgBox（加载项是）;我只是想解雇我打算创建一个Word文件，并根据需要调用一个宏，但不要使用SendKeys。

Is it possible to dismiss a MsgBox through VBA? I'm aware that I can create timed MsgBoxes in VBA but I'm not creating this MsgBox (the add-in is); I just want to dismiss it. I'm open to creating a Word file and calling a macro from that if required, but would prefer not to use SendKeys.

推荐答案

由于加载项和Excel / VBA在相同的上下文中运行，因此我们无法在相同的 VBA应用程序中启动它并监视其消息框，因为每个VBA应用程序都是一个<单线程流程。然而幸运的是，有一个解决方案可以利用不同的VBA应用程序在不同的上下文中运行，所以它们可以并行运行。

Since the "add-in" and Excel/VBA run in the same context, we cannot launch it and monitor its message-box within the same VBA application, because each VBA application is a single-threaded process. Fortunately however, there is a solution that can exploit the fact that different VBA applications run in different contexts, so they can run in parallel.

我建议的解决方案是创建一个MS-Word文档专门用于监视和关闭该消息框。我们需要Word（或任何其他办公应用程序），以使监视代码和加载代码在不同的上下文中并行运行。

My suggested solution is to create a MS-Word document that is dedicated to monitoring and closing that message box. We need this in Word (or any other office application) in order to make the monitoring code and the addin's code run in parallel, in different contexts.

1-创建一个Word宏启用文档，命名为 mboxKiller.docm 并将其放在某个文件夹中;即 C：\SO 在我的例子。将此代码放在 ThisDocument 并保存：

1- create a Word macro-enable document, named mboxKiller.docm and place it in some folder; i.e. C:\SO in my example. place this code in ThisDocument and save:

Private Declare PtrSafe Function FindWindow Lib "user32" Alias "FindWindowA" (ByVal lpClassName As String, ByVal lpWindowName As String) As Long
Private Declare PtrSafe Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, ByVal lParam As Long) As Long

Public Sub WaitAndKillWindow()
    On Error Resume Next
    Dim h As Long: h = FindWindow(vbNullString, "Microsoft Excel")
    If h <> 0 Then SendMessage h, 16, 0, 0 ' <-- WM_Close
    Application.OnTime Now + TimeSerial(0, 0, 1), "WaitAndKillWindow"
End Sub

Private Sub Document_Open()
    WaitAndKillWindow
End Sub

2-在Excel工作簿的VBA，使用以下代码创建一个名为 mboxKiller 的类模块：

2- In the Excel workbook's VBA, create a class module, named mboxKiller with this code:

Private killerDoc As Object

Private Sub Class_Initialize()
    On Error Resume Next
    Set killerDoc = CreateObject("Word.Application").Documents.Open(Filename:="C:\SO\mboxKiller.docm", ReadOnly:=True)
    If Err.Number <> 0 Then
        If Not killerDoc Is Nothing Then killerDoc.Close False
        Set killerDoc = Nothing
        MsgBox "could not lauch The mboxKiller killer. The message-box shall be closed manuallt by the user."
    End If
End Sub

Private Sub Class_Terminate()
    On Error Resume Next
    If Not killerDoc Is Nothing Then killerDoc.Application.Quit False
End Sub

3-测试和使用。在正常的类Module中，放置以下代码并测试过程

3- Testing and Usage. In a normal class Module, place the following code and test the procedure

Sub Test() ' <-- run this for testing after finishing the setup
    Dim killer: Set killer = New mboxKiller
    simulateAddin
    simulateAddin
    simulateAddin
End Sub

' Procedure supposed to do some calculation then display a message box
Private Sub simulateAddin()
    Dim i As Long
    For i = 0 To 1000: DoEvents: Next ' simulates some calculations
    MsgBox "This is a message box to simulate the message box of the addin." & VbCrLf & _
    "It will be automatically closed by the Word app mboxKiller"
End Sub

这篇关于以编程方式关闭MsgBox的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！