Search code examples
excelvbafocusmsgbox

Focus to MgsBox when Excel is not active

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 "..."
...
Solution

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