vba Excel 中的 MsgBox 焦点

MsgBox focus in Excel

提问by dan

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.

我正在 Excel 中使用 VBA 计算大量数据，并希望在完成后显示一个 MsgBox。MsgBox 实际上显示了计算所用的时间。

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.

问题是当用户决定在计算发生时做其他事情。Excel 继续计算，完成后，MsgBox 确实显示，但出于某种原因，Excel 没有将焦点放在 MsgBox 上。Excel 图标将在任务栏中闪烁，如果我们单击它，Excel 会最大化，但 MsgBox 位于 Excel 窗口后面，我们永远无法单击它。所以摆脱它的唯一方法是 taskkill excel.exe ......不是很好。Alt+Pause 也不起作用，因为代码只会在当前代码行之后停止，该行结束......当 MsgBox 关闭时。

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.

我AppActivate("Microsoft Excel")之前尝试过该功能但没有成功（如何将焦点放在 msgbox 上？）。应用程序名称实际上比该名称长，因为 Excel 2010 将文档名称添加到窗口标题中。

Any idea how I could get around this annoying problem?

知道如何解决这个烦人的问题吗？

采纳答案by dan

I tried most of all the other answers:

我尝试了大多数其他答案：

• ThisWorkbook.Activate
• AppActivate()
• Application.Wait()
• Sleeplibrary

• ThisWorkbook.Activate
• AppActivate()
• Application.Wait()
• Sleep图书馆

For whatever reason, none of the above works. I believe there is some really specific business setting in our computer environment that could be creating the problem. As others mentionned, all the above solutions should probably work on any brand new formatted Windows 7 install with any version of Office 2010 installed, even in a dual-monitor setup. So +1 to all of these answers. Also, I noticed the problematic only happens in some specific workbooks. Really weird behavior, that might just be an Office 2010 bug related to something I do in my workbook (whether it's VBA or simply Excel).

无论出于何种原因，上述方法均无效。我相信在我们的计算机环境中有一些非常具体的业务设置可能会造成问题。正如其他人所提到的，即使在双显示器设置中，上述所有解决方案都应该适用于安装了任何版本的 Office 2010 的任何全新格式化的 Windows 7 安装。所以对所有这些答案+1。另外，我注意到问题只发生在某些特定的工作簿中。非常奇怪的行为，这可能只是与我在工作簿（无论是 VBA 还是 Excel）中所做的事情相关的 Office 2010 错误。

With that being said, my real solution (which isn't really a solution to the initial problem) is not using MsgBox(). A couple of workarounds do exist and I found out that Forms are the best one for me. So instead of wasting more time in this problem, I came out with the really simple following code to replace my original MsgBox():

话虽如此，我真正的解决方案（这并不是最初问题的真正解决方案）不是使用MsgBox(). 确实存在一些解决方法，我发现 Forms 对我来说是最好的。因此，我没有在这个问题上浪费更多时间，而是用以下非常简单的代码来替换我原来的代码MsgBox()：

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

I can put whatever text I want in a label in frmMsgBox, and since this is Excel, I can simply pass parameters by using hidden cells.

我可以将我想要的任何文本放在 . 中的标签中frmMsgBox，因为这是 Excel，我可以简单地使用隐藏单元格传递参数。

Thanks for all the help.

感谢所有的帮助。

回答by DeerSpotter

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

无论哪个其他应用程序具有焦点，这都将在 Excel 中工作：

Before the message box or any warning put the following code:

在消息框或任何警告之前放置以下代码：

AppActivate Application.Caption
DoEvents

AppActivate Application.Caption
DoEvents

Trust me on this, this is amazing!

相信我，这太棒了！

回答by Charles Byrne

Sounds like the macro process is making the app unresponsive. Not certain if this will help but have you considered adding DoEvents or Sleep (API call) in your long running process to yield control back to the OS? Sleep is an API call so you'd need to declare it in a module to use it. DoEvents prevents the app from locking up, but it does use more CPUs so if it is in a loop I would access it every once in a while (30% or less of the iterations). If it isn't a loop and you know where the bottlenecks are in your long running process you can call DoEvents after each long running process.

听起来像宏过程使应用程序无响应。不确定这是否会有所帮助，但您是否考虑过在长时间运行的进程中添加 DoEvents 或 Sleep（API 调用）以将控制权交还给操作系统？Sleep 是一个 API 调用，因此您需要在模块中声明它才能使用它。DoEvents 可防止应用程序锁定，但它确实使用了更多的 CPU，因此如果它处于循环中，我会每隔一段时间（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

Sleep API source

睡眠 API 源

Then in your process

然后在你的过程中

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

I'd suggest setting the Application.ScreenUpdating = False then turn it back on after the process has finished, but it may make matters worse.

我建议设置 Application.ScreenUpdating = False 然后在过程完成后重新打开它，但这可能会使事情变得更糟。

Update

更新

Just read the comments that were entered while typing my answer. Another option instead of the messsage box would be to open the folder window where the files are being saved after all the files have been created (replace Environ$("APPDATA") with save location):

只需阅读在输入我的答案时输入的评论。另一种替代消息框的选项是在创建所有文件后打开保存文件的文件夹窗口（将 Environ$("APPDATA") 替换为保存位置）：

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

OR open one of the PDFs:

或打开其中一个 PDF：

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

Another Option

另外一个选项

I couldn't put this in the comments, because there was too much code, but Make an API call to MessageBox instead, but don't set owner of the message box (hWnd) set it at &H0 or &O0. The vbSystemModal should make it pop to the top. I don't know if it will allow you to select the excel application window after the user clicks okay:

我不能把它放在评论中，因为代码太多，而是对 MessageBox 进行 API 调用，但不要设置消息框的所有者 (hWnd) 将其设置为 &H0 或 &O0。vbSystemModal 应该让它弹出到顶部。不知道能不能让你在用户点击ok后选择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

回答by guitarthrower

I did some testing, and found a potential work around for you.

我做了一些测试，并为您找到了一个潜在的解决方法。

I set up this simple procedure to test your situation:

我设置了这个简单的程序来测试您的情况：

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

I run this, then minimize all windows, and when the timer is up, nothing happens. If I switch to Excel I can see the Message Box, but nothing otherwise.

我运行它，然后最小化所有窗口，当计时器到时，什么也没有发生。如果我切换到 Excel，我可以看到消息框，但没有其他内容。

So I tried this:

所以我试过这个：

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

This time when I run the procedure, then minimize all windows, instead of seeing nothing the Message Box pops up(but not the Excel Window).

这次当我运行该程序时，然后最小化所有窗口，而不是看到消息框弹出（但不是 Excel 窗口）。

I think by adding ThisWorkbook.Activateright before your MsgBoxcode you can have the same happen in your file.

我认为通过ThisWorkbook.Activate在你的MsgBox代码之前添加你可以在你的文件中发生同样的事情。

It doesn't quite get you all the way there, but hopefully is better than where you are at.

它并没有让你一路走到那里，但希望比你现在的位置更好。

回答by mrbungle

*oops didn't read the rest of your question, I used AppActivate and worked as expected and Im using MS Office Professional Plus 2010

*oops 没有阅读您的其余问题，我使用了 AppActivate 并按预期工作，我使用的是 MS Office Professional Plus 2010

I couldn't get the method from guitarthrower to work but was able to get this to work as an example. Instead of ThisWorkbook.Activatetry AppActivate ("Microsoft excel")then your MsgBox

我无法从吉他手那里得到这个方法，但是作为一个例子，我能够让它发挥作用。而不是ThisWorkbook.Activate尝试AppActivate ("Microsoft excel")然后你的MsgBox

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