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

Sounds like the macro process is making the app unresponsive. Not certain if this will help but have you considered adding DoEvents or Sleep (API call) in your long running process to yield control back to the OS? Sleep is an API call so you'd need to declare it in a module to use it. DoEvents prevents the app from locking up, but it does use more CPUs so if it is in a loop I would access it every once in a while (30% or less of the iterations). If it isn't a loop and you know where the bottlenecks are in your long running process you can call DoEvents after each long running process.

#If VBA7 And Win64 Then
' 64 bit Excel
Public Declare PtrSafe Sub Sleep Lib "kernel32" ( _
    ByVal dwMilliseconds As LongLong)
#Else
' 32 bit Excel
Public Declare Sub Sleep Lib "kernel32" ( _
    ByVal dwMilliseconds As Long)
#End If

Sleep API source

Then in your process

Sub SomeLongProcessWithDoEventsExample()
   For i = 1 to 100000
       'Some lengthy code
       If i Mod 333 = 0 Then
          DoEvents
       End If
   Next i
End Sub

Sub SomeLongProcessWithSleepExample()
   For i = 1 to 100000
       'Some lengthy code
       If i Mod 333 = 0 Then
          Sleep 1 * 1000 'Millseconds
       End If
   Next i      
End Sub

I'd suggest setting the Application.ScreenUpdating = False then turn it back on after the process has finished, but it may make matters worse.

Update

Just read the comments that were entered while typing my answer. Another option instead of the messsage box would be to open the folder window where the files are being saved after all the files have been created (replace Environ$("APPDATA") with save location):

Shell "explorer.exe" & " " & Environ$("APPDATA"), vbMaximizedFocus

OR open one of the PDFs:

Shell Environ$("COMSPEC") & " /c Start C:\SomeFile.pdf", vbMaximizedFocus

Another Option

I couldn't put this in the comments, because there was too much code, but Make an API call to MessageBox instead, but don't set owner of the message box (hWnd) set it at &H0 or &O0. The vbSystemModal should make it pop to the top. I don't know if it will allow you to select the excel application window after the user clicks okay:

MessageBox &O0, "My Message", "My Caption", vbOKOnly + vbSystemModal


#If VBA7 And Win64 Then
Public Declare PtrSafe Function MessageBox _
    Lib "User32" Alias "MessageBoxA" _
       (ByVal hWnd As LongLong, _
        ByVal lpText As String, _
        ByVal lpCaption As String, _
        ByVal wType As LongLong) _
    As Long

#Else
Public Declare Function MessageBox _
    Lib "User32" Alias "MessageBoxA" _
       (ByVal hWnd As Long, _
        ByVal lpText As String, _
        ByVal lpCaption As String, _
        ByVal wType As Long) _
    As Long

#End If

Answer 3

*oops didn't read the rest of your question, I used AppActivate and worked as expected and Im using MS Office Professional Plus 2010

I couldn't get the method from guitarthrower to work but was able to get this to work as an example. Instead of ThisWorkbook.Activate try AppActivate ("Microsoft excel") then your MsgBox

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

Answer 4

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.

Answer 5

I tried most of all the other answers:

• ThisWorkbook.Activate
• AppActivate()
• Application.Wait()
• Sleep library

For whatever reason, none of the above works. I believe there is some really specific business setting in our computer environment that could be creating the problem. As others mentionned, all the above solutions should probably work on any brand new formatted Windows 7 install with any version of Office 2010 installed, even in a dual-monitor setup. So +1 to all of these answers. Also, I noticed the problematic only happens in some specific workbooks. Really weird behavior, that might just be an Office 2010 bug related to something I do in my workbook (whether it's VBA or simply Excel).

With that being said, my real solution (which isn't really a solution to the initial problem) is not using MsgBox(). A couple of workarounds do exist and I found out that Forms are the best one for me. So instead of wasting more time in this problem, I came out with the really simple following code to replace my original MsgBox():

Application.ScreenUpdating = False
frmMsgBox.Show
Application.ScreenUpdating = True

I can put whatever text I want in a label in frmMsgBox, and since this is Excel, I can simply pass parameters by using hidden cells.

Thanks for all the help.