停用 Excel VBA 用户窗体
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14376251/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
deactivate Excel VBA userform
提问by codeomnitrix
I am having some macro in Excel vba and in that I am performing some functions on the excel sheets which takes around 30 seconds to complete. So I want to show a user form with a progress bar during that span of time.
我在 Excel vba 中有一些宏，并且我正在 Excel 工作表上执行一些需要大约 30 秒才能完成的功能。所以我想在这段时间内显示一个带有进度条的用户表单。
I tried using userform.showin very start of the function and userform.hideat the end but I found that No action can be performed in background.
我尝试userform.show在函数的最开始和userform.hide最后使用，但我发现无法在后台执行任何操作。
So just want to know if there is any turn around to let the processing be done in the background while the form is being displayed.
所以只想知道是否有任何转身让处理在显示表单时在后台完成。
Many thanks :)
非常感谢 ：）
Private Sub CommandButton1_Click()
'--------------Initialize the global variables----------------
UserForm1.Show
nameOfSheet2 = "Resource Level view"
nameOfSheet3 = "Billable Hours"
nameOfSheet4 = "Utilization"
'-------------------------------------------------------------
Dim lastRow, projectTime, nonProjectTime, leaveAndOther
Dim loopCounter, resourceCounter
lastRow = 0
projectTime = 0
nonProjectTime = 0
leaveAndOther = 0
resourceCounter = 2
Set workbook1 = Workbooks.Open(File1.Value)
Sheet3Creation
Sheet2Creation
Sheet4Creation
UserForm1.Hide
End Sub
回答by bonCodigo
The usage of Progress Bar is to show the progress of currently running code. And I wouldn't know if anyone want to do anything with the sheet while the code is running...
Progress Bar 的用途是显示当前运行代码的进度。而且我不知道是否有人想在代码运行时对工作表做任何事情......
Anyway if you want to interact with the sheet while Form is displaying you may try to add the following code:
无论如何，如果您想在 Form 显示时与工作表进行交互，您可以尝试添加以下代码：
UserForm.Show vbvModeless
And to update a Modelessform you must add DoEventswithin your subroutine.
要更新Modeless表单，您必须DoEvents在子程序中添加。
When you want to close the form at the end, do this:
当您想在最后关闭表单时，请执行以下操作：
UserForm.Unload
Here is what I would do:
这是我会做的：
Click a button to run your macro
单击按钮以运行您的宏
Private Sub Button1_Click()
Call userform.show vbMmodeless
End Sub
Private Sub UserForm_activate()
Call Main '-- your macro name
End Sub
Sub Main()
'-- your code
DoEvents '-- to update the form *** important
useroform.Unload
End Sub
After OP showed his code:
在 OP 展示了他的代码之后：
Why do we need a progress bar?
为什么我们需要进度条？
In your case I do not really see that you are using any sort of heaving codes running at the background. So adding a progress bar could make your code slow as to update it, you may be calling an extra loop... check this reference article if you really want to have the progress bar :),
You can also use
Application.StatusBarto display a message.The other is to use
Timeror a littel bit more technical way would be to wrap system timer ticks and refresh/update form accordingly. In VBA Excel we don't get that lucky as for C# or VB..VBA Macro On Timer style to run code every set number of seconds, i.e. 120 seconds.
在您的情况下，我并没有真正看到您正在使用在后台运行的任何类型的提升代码。因此，添加进度条可能会使您的代码更新速度变慢，您可能会调用一个额外的循环...如果您真的想要进度条，请查看此参考文章:)，
另一种是使用
Timer或稍微有点技术性的方法是包装系统计时器滴答声并相应地刷新/更新表单。在 VBA Excel 中，我们没有 C# 或 VB 那样幸运。
