MsgBox focus in Excel

vba excel excel-2010 msgbox
dan picture dan · Dec 4, 2014 · Viewed 8.5k times · Source

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

DeerSpotter picture DeerSpotter · Nov 16, 2015

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!

Related questions

Excel VBA Run-time error '13' Type mismatch

I created a macro for a file and first it was working fine, but today I've been opening and restarting the file and macro hundreds of times and I'm always getting the following error: Excel VBA Run-time error '13' …

Read More
Copy an entire worksheet to a new worksheet in Excel 2010

I have found similar questions that deal with copying an entire worksheet in one workbook and pasting it to another workbook, but I am interested in simply copying an entire worksheet and pasting it to a new worksheet -- in …

Read More
Copying and pasting data using VBA code

I have a button on a spreadsheet that, when pressed, should allow the user to open a file, then copy columns A-G of the spreadsheet "Data", then paste the data from those columns on the current sheet. I have a …

Read More