Focus MsgBox in Excel

Question

I am computing a lot of data using VBA in Excel and want to show MsgBox when this is done. MsgBox actually shows the time it takes to calculate.

The problem is when the user decides to do something else while calculating. Excel continues to compute, and when this is done, MsgBox does show, but for some reason Excel does not give MsgBox focus. The Excel icon will flash on the taskbar, and if we click on it, Excel will maximize, but the MsgBox is outside the Excel window, and we NEVER can click it. So the only way to get out of it is excel.exe task ... not very good. Alt + Pause does not work, as the code will be stopped only after the current line of code that ends ... when the MsgBox is closed.

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

Any idea how I could get around this nasty problem?

Answer 1

I tried most of the other answers:

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

- . , -, . , , , Windows 7 Office 2010, . , +1 . , , . , Office 2010, , ( VBA Excel).

( ) MsgBox(). , , Forms . , , , MsgBox():

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

frmMsgBox, Excel, .

.

Answer 2

, . , , , DoEvents Sleep ( API) , ? Sleep - API, . DoEvents , , , I (30% ). , , , DoEvents .

#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

API

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

Application.ScreenUpdating = False, , .

, . messsage - , ( Environ $( "APPDATA" ) ):

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

PDF :

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

, , API MessageBox, (hWnd), & H0 &; O0. VbSystemModal pop up. , excel , :

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

.

:

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

, , , . Excel, , .

, :

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

, , , , , ( Excel).

, ThisWorkbook.Activate MsgBox, .

, , , , , .

Answer 4

* oops , AppActivate , MS Office Professional Plus 2010

I could not get the method from guitarthrower to work, but I was able to get this to work as an example. Instead ThisWorkbook.Activatetry AppActivate ("Microsoft excel"), then yourMsgBox

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

Answer 5

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

Before the message box or any warning, enter the following code:

AppActivate Application.Caption
DoEvents

Believe me, this is awesome!