I figured it out! Import the following module into your VBE and it will expose a subroutine called Alert (or modAlert.Alert) which has as arguments: (Message,Title,Number of seconds before it disappears).
If the number of seconds is 0 or missing it will stay on screen until you click OK. This does exactly as you wish, it will pop up on top of any open application and will run in a separate thread than your macro so it will not halt your code at all (except for the slight time it takes to run its code of course).
IMPORTANT: In Excel 2002 and newer you will have to explicitly trust access to your VBA Projects. Go to Tools, Macro, Security. Click on the 2nd tab: Trusted Publishers. In the lower left, choose Trust Access to Visual Basic Project. In Excel 2007, Go to Office Icon, Excel Options, Trust Center, Trust Center Settings, Macro Settings, and choose Trust access to the VBA project object model.
If you have any questions, feel free to ask.modAlert_V.2.zip
EDIT: Changed subroutine in modAlert as I forgot to do some cleaning up after the code was run, this should fix that. Replace subroutine CreateAlertObject with this:
or use this updated .bas file:
Public Sub CreateAlertObject(sText As String, sTitle As String, lNumSeconds As Long)
CreateObject("Wscript.Shell").popup sText, lNumSeconds, sTitle, vbSystemModal + 64