Focus to MgsBox when Excel is not active

Question

I have checked related questions such as this or this one but the solutions there do not seem to solve my problem.

I am running a VBA script on my computer. The script takes a few minutes to execute and while waiting I am checking other things in my computer. To get my attention once the script has finished running, I have included a MsgBox at the end of my script. However, because Excel is not active/selected when the script finishes, I cannot see it - only when I reactivate/select Excel.

How can I bring into focus the MsgBox when Excel is not active? I have already tried the following tweaks but they do not work:

• ThisWorkbook.Activate:

  ...
  ThisWorkbook.Activate
  MsgBox "..."
  ...
  

• AppActivate() (this command threw an error):

  ...
  AppActivate("Microsoft excel")
  MsgBox "..."
  ...
  

Answer 1

How about playing a sound when the program finishes? Place this declaration at the top of a standard code module, above any procedures existing there.

Public Declare Function Beep Lib "kernel32" _
              (ByVal dwFreq As Long, _
               ByVal dwDuration As Long) As Long

If you place this procedure in the same module you may not need it to be public. Adjust pitch and duration to your preference.

Sub EndSound()
    Beep 500, 1000
End Sub

Then place the procedure call at the end of your program.

Call EndSound

I suppose you might use a more elaborate sound - may I suggest a couple of bars from Beethoven's 5th? Modify the EndSound procedure. Chip Pearson has more on this idea.

Answer 2

Try:

Application.WindowState = xlMaximized

Answer 3

Disclaimer: This is not my code and I do not know who the author is. I had this code in my database.

Put your code in Sub Sample(). I have shown where you can insert your code. Once the code is run, Excel will flash 5 times. you can change this number by changing Private Const NumberOfFlashes = 5

Paste this in a Module.

Option Explicit

Private Type FLASHWINFO
    cbSize As Long
    Hwnd As Long
    dwFlags As Long
    uCount As Long
    dwTimeout As Long
End Type

Private Const FLASHW_STOP As Long = 0
Private Const FLASHW_CAPTION As Long = &H1
Private Const FLASHW_TRAY As Long = &H2
Private Const FLASHW_ALL As Long = (FLASHW_CAPTION Or FLASHW_TRAY)
Private Const FLASHW_TIMER As Long = &H4
Private Const FLASHW_TIMERNOFG As Long = &HC
Private FLASHW_FLAGS As Long

Private Declare Function LoadLibrary Lib "kernel32" _
Alias "LoadLibraryA" (ByVal lpLibFileName As String) As Long

Private Declare Function GetProcAddress Lib "kernel32" _
(ByVal hModule As Long, ByVal lpProcName As String) As Long

Private Declare Function FreeLibrary Lib "kernel32" _
(ByVal hLibModule As Long) As Long

Private Declare Function FlashWindowEx Lib "user32" _
(FWInfo As FLASHWINFO) As Boolean

Private Declare Function FindWindow Lib "user32" Alias "FindWindowA" _
(ByVal lpClassName As String, ByVal lpWindowName As String) As Long

Private Const NumberOfFlashes = 5

Private Function APIFunctionPresent(ByVal FunctionName _
   As String, ByVal DllName As String) As Boolean

    Dim lHandle As Long
    Dim lAddr  As Long

    lHandle = LoadLibrary(DllName)
    If lHandle <> 0 Then
        lAddr = GetProcAddress(lHandle, FunctionName)
        FreeLibrary lHandle
    End If

    APIFunctionPresent = (lAddr <> 0)

End Function

Sub Sample()
    '
    ' Put your code here. Once that code finishes, Excel will FLASH
    '

    Dim udtFWInfo As FLASHWINFO

    If Not APIFunctionPresent("FlashWindowEx", "user32") Then Exit Sub

    With udtFWInfo
       .cbSize = Len(udtFWInfo)
       .Hwnd = Application.Hwnd
       .dwFlags = FLASHW_FLAGS Or FLASHW_TRAY
       .uCount = NumberOfFlashes
       .dwTimeout = 0
    End With

    Call FlashWindowEx(udtFWInfo)

    MsgBox "Done"
End Sub

Answer 4

The easiest way is to probably to create a userform instead then set the focus to this when it initialises.

Code in the userform to show as modal:

Private Declare Function FindWindow Lib "User32" Alias "FindWindowA" (ByVal lpClassName _
    As String, ByVal lpWindowName As String) As Long
Private Declare Function SetWindowPos Lib "User32" (ByVal hwnd As Long, ByVal hWndInsertAfter As Long, _
    ByVal X As Long, ByVal Y As Long, ByVal cx As Long, ByVal cy As Long, ByVal wFlags As Long) As Long

Private Const SWP_NOSIZE = &H1
Private Const SWP_NOMOVE = &H2
Private Const FLAGS As Long = SWP_NOMOVE Or SWP_NOSIZE
Private Const HWND_TOPMOST = -1

Private Sub UserForm_Initialize()
    Dim hwnd As Long: hwnd = FindWindow(vbNullString, Me.Caption)
    If hwnd > 0 Then SetWindowPos hwnd, HWND_TOPMOST, 0, 0, 0, 0, FLAGS   ' Set Modal
End Sub