vba “请稍候”弹出消息

"Please Wait" popup message

提问by Fwafa

I am trying to create a Please Waitmessage in Excel VBA. This is to remind the user processing a SQL query that the process is still ongoing. I would like to have this message displayed in a userform.

我正在尝试Please Wait在 Excel VBA 中创建一条消息。这是为了提醒处理 SQL 查询的用户该过程仍在进行中。我想在用户表单中显示此消息。

Currently, the user connects to SQL DB, selects database and some other options, presses Go... and the form does not unload. At the Go point I have another form which pops up with message Please Wait, but this form does not unload, i.e. the SQL execute command does not run.

目前，用户连接到 SQL DB，选择数据库和其他一些选项，按 Go... 并且表单没有卸载。在 Go 点，我有另一个弹出消息的Please Wait表单，但该表单不会卸载，即 SQL 执行命令不会运行。

My code for calling the Please Waituserform, including a label:

我调用用户Please Wait表单的代码，包括一个标签：

   Call WaitShowSQl.ExecuteCall KillWaitCall WaitShow
   SQl.Execute
   Call KillWait

In another code module I have:

在另一个代码模块中，我有：

   Sub WaitShow()
   Wait.Show
   End Sub

   Sub KillWait()
   Unload Wait
   End Sub

I have userform called waitwith following code:

我wait使用以下代码调用了用户表单：

Private Sub UserForm_Activate()
Call PleaseWait
End Sub 

Sub PleaseWait()
Wait.Label1.Caption = "Calculating...Please Wait!"
End Sub

Now when executing the code, the run-time does not move back through the module in order to execute the following:

现在在执行代码时，运行时不会通过模块向后移动以执行以下内容：

SQl.ExecuteSQl.Execute

I can show the progress of the program with a status bar, but a userform popup message would be very useful for this program.

我可以使用状态栏显示程序的进度，但是用户窗体弹出消息对于该程序非常有用。

采纳答案by GTG

The default behavior of the Form.Show method is to show the form as a modal dialog. What you are looking for is a modeless dialog box, so you need to specify that, like this:

Form.Show 方法的默认行为是将表单显示为模式对话框。您正在寻找的是一个无模式对话框，因此您需要指定它，如下所示：

Sub WaitShow()
   Wait.Show vbModeLess
End Sub

Update:

更新：

If you can't user a modeless dialog, you have other options, as mentioned in a comment. One of them is to let the wait userform do the SQL executing. This could be done like this:

如果您不能使用无模式对话框，您还有其他选项，如评论中所述。其中之一是让等待用户表单执行 SQL。这可以像这样完成：

In the wait form, first declare variables

在等待形式中，首先声明变量

Private mySomeParameter1 As String
Private mySomeReturnValue1 As String

'Input parameters to the form
Public Sub Init(ByVal some_parameter1 As String, ...)
... Initialize your SQL, NOT execute it
End Sub

'Output values from the from
Public Sub GetReturns(ByRef some_return_value1 As String, ...)
... set the output parameters...
End Sub

'Do the work
Private Sub UserForm_Activate()
  Call PleaseWait
  'Either call DoEvents or a manual Refresh to let the label display itself
  Call ExecutSQL    ' A function that executes the SQL
  Unload Me
End Sub

And in the main form:

并以主要形式：

Sub WaitShow()
   Wait.Init(the parameters...)
   Wait.Show
   Wait.GetReturns(the results...)
End Sub

Yet another option for you would be to skip the Wait form completely and instead show an "Waiting" Image on the mainform and hide it when the SQL processing is done.

您的另一个选择是完全跳过等待表单，而是在主表单上显示“等待”图像，并在 SQL 处理完成后隐藏它。

回答by Jon Crowell

Here's how to show a progress bar while your users are waiting for Excel to finish processing:

以下是在用户等待 Excel 完成处理时显示进度条的方法：

https://stackoverflow.com/a/10791349/138938

https://stackoverflow.com/a/10791349/138938

In your case, you can guess a reasonable "average" time for your queries and have the progress bar update with percentages of that time, which will reassure your users that Excel is working and they don't need to panic.

在您的情况下，您可以猜测查询的合理“平均”时间，并使用该时间的百分比更新进度条，这将使您的用户确信 Excel 正在运行，他们无需惊慌。

回答by user3108141

My solution is far less elegant and impressive than the others. FYI, my script is doing processing for a number of partners within a FOR...NEXT loop, and I want to update the screen with the partner currently being processed.

我的解决方案远不如其他解决方案优雅和令人印象深刻。仅供参考，我的脚本正在为 FOR...NEXT 循环中的多个合作伙伴进行处理，我想用当前正在处理的合作伙伴更新屏幕。

I created a worksheet tab called "Messages". I expanded cell A1 as wide and tall as I could, and I picked a font that I liked a color scheme that I liked. My script starts by turning off ScreenUpdating. I then

我创建了一个名为“消息”的工作表选项卡。我将单元格 A1 扩展到尽可能宽和高，并选择了一种我喜欢的字体，我喜欢的配色方案。我的脚本首先关闭 ScreenUpdating。然后我

Sheets("Messages").Select
Cells(1, 1).Value = "Processing " & Partner
Application.ScreenUpdating = True
Application.ScreenUpdating = False

After this, I return to whatever sheet I need. Nothing fancy, but it's fast, easy, and does what I need. :)

在此之后，我返回到我需要的任何工作表。没什么特别的，但它快速、简单，并且可以满足我的需求。:)

回答by Tony McGee

I use a command button on the form set to a very small size, hidden when the form loads, with the caption "Please wait, processing..."

我在表单上使用了一个设置为非常小的命令按钮，在表单加载时隐藏，标题为“请稍候，正在处理...”

I resize the button in the form Iniliatlise event (I simply resize to the form size using the top, left, width & height properties). YOu can of course make it whatever size you like.

我在表单 Iniliatlise 事件中调整按钮的大小（我只是使用顶部、左侧、宽度和高度属性调整到表单大小）。你当然可以把它做成你喜欢的任何尺寸。

I make the command button visible just before my time consuming code & make it hidden again at the end of the code.

我使命令按钮在我耗时的代码之前可见，并在代码末尾再次隐藏。

Requires some "DoEvents", but works perfectly.

需要一些“DoEvents”，但效果很好。