MsgBox focus in Excel

Question

I am calculating a lot of data with VBA in Excel and want to show a MsgBox when it's done. The MsgBox actually shows the time it took for the calculation.

The problem is when the user decides to do something else while the computation happens. Excel continues to calculate, and when it's done, the MsgBox does show but for some reason, Excel doesn't give the focus to the MsgBox. The Excel icon will blink in the taskbar and if we click it, Excel does maximize, but the MsgBox is behind the Excel window and we can NEVER click it. So the only way to get out of it is to taskkill excel.exe... not really nice. Alt+Pause doesn't work either since the code will be stopped only after the current line of code, which ends... when the MsgBox is closed.

I tried the function AppActivate("Microsoft Excel") before without any success (How do I bring focus to a msgbox?). The application name is actually longer than that since Excel 2010 adds the document name to the window title.

Any idea how I could get around this annoying problem?

Answer 1

This will work in Excel no matter which other application has focus:

Before the message box or any warning put the following code:

AppActivate Application.Caption
DoEvents

Trust me on this, this is amazing!

Answer 2

I did some testing, and found a potential work around for you.

I set up this simple procedure to test your situation:

Sub test()
    If Application.Wait(Now + TimeValue("0:00:10")) Then
        MsgBox "Time expired"
    End If
End Sub

I run this, then minimize all windows, and when the timer is up, nothing happens. If I switch to Excel I can see the Message Box, but nothing otherwise.

So I tried this:

Sub test()
    If Application.Wait(Now + TimeValue("0:00:10")) Then
        ThisWorkbook.Activate
        MsgBox "Time expired"
    End If
End Sub

This time when I run the procedure, then minimize all windows, instead of seeing nothing the Message Box pops up (but not the Excel Window).

I think by adding ThisWorkbook.Activate right before your MsgBox code you can have the same happen in your file.

It doesn't quite get you all the way there, but hopefully is better than where you are at.