Programmatically dismiss a MsgBox

Question

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.

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.

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.

What type of add-in? You don't have any way to modify how it's called ? — Tim Williams, Jan 24 '17 at 05:37
I can call the add-in manually, on a timer or on open (all through their settings, I can't interact with it directly through VBA) — Sam Woolerton, Jan 24 '17 at 19:13

A.S.H · Accepted Answer · 2017-06-25T12:32:38.417

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.

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- 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- 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- 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

Realised that this involves a MsgBox rather than a different window, so I can't use FindWindow on it. If you could edit your answer to clarify whether MsgBoxes can be programmatically dismissed, I'll accept it — Sam Woolerton, Jan 24 '17 at 23:06
I started a messagebox from word and could close it from VBA/Excel like this: `h = WaitMyWindow("Microsoft Word", 10) If h <> 0 Then SendMessage h, 16, 0, 0` (p.s. *Microsoft Word* was the title of the message-box window). — A.S.H, Jan 24 '17 at 23:21
While the WaitMyWindow function is running it pauses all other VBA code. I've updated my question to show my latest attempts — Sam Woolerton, Jan 25 '17 at 03:25
Working flawlessly thanks! It doesn't exit though, causing the Word file to remain read-only - please see my last edit — Sam Woolerton, Jan 25 '17 at 23:41
It seems I've had a typo when pasting the code in the `Sub Class_Terminate()`.. has to be `killerDoc.Application.Quit` instead of `killerDoc.App.Quit`. That routine is aimed to close the word app when the class's mboxKiller object goes out of scope. — A.S.H, Jan 25 '17 at 23:48
I'm not sure that we need to do timing in the last solution. However it's in your hands now, you can build on it if you find that something additional is useful. Thanks for the interesting problem :) — A.S.H, Jan 25 '17 at 23:57
After fixing the typo it closes itself now so no need for my timing solution. Cheers for sticking with it to the end! — Sam Woolerton, Jan 26 '17 at 00:18

score 0 · Answer 2 · answered Jul 21 '21 at 00:34

0

VBA also has the ability to temporarily dismiss alerts.

Application.DisplayAlerts = False
'while you run your code here, no alerts will be displayed
Application.DisplayAlerts = True

answered Jul 21 '21 at 00:34

heather

1

Programmatically dismiss a MsgBox

2 Answers2

Linked