停用Excel VBA用户窗体 [英] deactivate Excel VBA userform

问题描述

我在Excel vba中有一些宏，因为我在excel表上执行一些功能，大约需要30秒才能完成。所以我想在这段时间内显示一个进度条的用户表单。

我尝试使用 userform.show 在函数的非常开始和 userform.hide 中，但我发现在后台不能执行任何操作。

所以只是想知道是否有任何转向，以便在窗体显示时在后台完成处理。

非常感谢： ）

  Private Sub CommandButton1_Click（）
'--------------初始化全局变量---------------- 
 
 UserForm1.Show 
 
 
 
 nameOfSheet2 =资源级别视图
 nameOfSheet3 =可结算小时
 nameOfSheet4 =利用率
'----------------------- -------------------------------------- 
 Dim lastRow，projectTime，nonProjectTime，leaveAndOther 
 Dim loopCounter，resourceCounter 
 lastRow = 0 
 projectTime = 0 
 nonProj ectTime = 0 
 leaveAndOther = 0 
 resourceCounter = 2 
设置工作簿1 = Workbooks.Open（File1.Value）
 Sheet3创建
 Sheet2创建
 Sheet4创建
 UserForm1.Hide 
 
 End Sub 
  

解决方案

进度栏的用法是显示当前正在运行的代码的进度。而且我不知道有没有人想要在代码运行时对该表进行任何操作...

无论如何，如果要与表单交互，而Form是显示您可能会尝试添加以下代码：

  UserForm.Show vbvModeless 
  / pre> 
 
 
要更新 Modeless 表单，您必须添加  DoEvents  。
 
 
 
当您想关闭窗体时，请执行以下操作：
  UserForm.Unload 
  
这里是我会做的：
 
 
 
点击一个按钮来运行宏
  Private Sub Button1_Click（）
调用userform.show vbMmodeless 
 End Sub 
 
 Private Sub UserForm_activate（）
调用Main' - 你的宏名
 End Sub 
 
 Sub Main（）
' - 你的代码
 DoEvents' - 更新表单*** important 
 
 useroform。卸载
 End Sub 
  
 
 
 
 
 
 
 OP显示他的代码后：
 
 
 
为什么需要一个进度条？
 
 
 
 
 
  当宏需要很长时间运行时，人们会紧张。它崩溃了吗需要多长时间？我有时间跑到浴室吗？放松... 
 
 
 
 
 
 
 
 
在你的情况下没有真正看到你正在使用任何在后台运行的代码。所以添加一个进度条可能会使您的代码缓慢，以更新它，你可能会调用一个额外的循环... 如果您真的想要进度条，请检查此参考文章:) ，
 
 
 
 
您还可以使用 Application.StatusBar 显示消息 
 
 
 
 
另一个是使用计时器或者更有技术的方法是相应地包装系统计时器滴答和刷新/更新表单。在VBA Excel中，我们没有像C＃或VB那样幸运。
 
 
 
 
  VBA Macro On Timer风格每设定的秒数运行代码，即120秒。
 
 
 
 
 如何在Excel中显示运行时钟？ 
 
 
 
 
 如何测试VBA代码的运行时间？ 
 
 
 
 
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.

I tried using userform.show in very start of the function and userform.hide at the end but I found that No action can be performed in background.

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

解决方案
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...

Anyway if you want to interact with the sheet while Form is displaying you may try to add the following code:
 UserForm.Show vbvModeless
And to update a Modeless form you must add DoEvents within your subroutine.

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:

Why do we need a progress bar?


  
When macros take a long time to run, people get nervous. Did it crash? How much longer will it take? Do I have time to run to the bathroom? Relax...



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.StatusBar to display a message.
The other is to use Timer or 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.
How do I show a running clock in Excel?
How do you test running time of VBA code?


                        
这篇关于停用Excel VBA用户窗体的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！