vba 绘制完UserForm后自动执行一些代码
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9976680/
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
Automatically execute some code after the UserForm is drawn
提问by Cutter
I have created a UserForm where the user is required to fill-in three fields. The macro attempts to auto-detect these fields' values in the UserForm_Initialize()event, then displays the found values in the three fields, but the user can change them. The auto-detection takes a few seconds, though, and delays the appearance of the UserForm. I'd like the UserForm to appear with its fields blank before the auto-detection procedure, then have the auto-detection procedure fill the fields automatically. What would be the best way to do this? Making the UserForm non-modal makes the macro run without waiting for the user's input, which is problematic. I don't want to have an "auto-detect" button: this has to be done automatically.
我创建了一个用户表单，用户需要填写三个字段。宏尝试自动检测UserForm_Initialize()事件中这些字段的值，然后在三个字段中显示找到的值，但用户可以更改它们。但是，自动检测需要几秒钟，并且会延迟用户窗体的出现。我希望用户窗体在自动检测程序之前显示其字段为空白，然后让自动检测程序自动填充字段。什么是最好的方法来做到这一点？使 UserForm 非模态使宏运行而无需等待用户的输入，这是有问题的。我不想有一个“自动检测”按钮：这必须自动完成。
回答by Siddharth Rout
Use the Activate()event instead of Initialize():)
使用Activate()事件而不是Initialize():)
Private Sub UserForm_Activate()
End Sub
FOLLOWUP
跟进
Thanks! It works, but there seems to be a bug: the dialog is drawn all white until the macro completes. screenshot (the dialog should be gray)
谢谢！它有效，但似乎有一个错误：在宏完成之前，对话框被绘制成全白色。屏幕截图（对话框应该是灰色的）
No. It is not a bug :) Try This. Add Doeventsas shown below.
不，这不是错误 :) 试试这个。Doevents如下图添加。
Private Sub UserForm_Activate()
UserForm1.ProgressBar1.Value = 0
starttime = Timer
While Timer - starttime < 1
UserForm1.ProgressBar1.Value = (Timer - starttime) * 100
DoEvents
Wend
End Sub
HTH
HTH
Sid
锡德
回答by Tom Juergens
I'd suggest using a timer. Open the form with the input fields disabled and empty, and set the timer to fire within a couple of hundred milliseconds. This should allow the form to be displayed immediately. Do your auto-detection within the timer's tick event (disable the timer first), then enable the fields and fill in the detected values.
我建议使用计时器。打开输入字段禁用且为空的表单，并将计时器设置为在几百毫秒内触发。这应该允许立即显示表单。在计时器的滴答事件中进行自动检测（首先禁用计时器），然后启用字段并填写检测到的值。
回答by R Frankham
There is a simpler way to do this...
有一种更简单的方法可以做到这一点......
1) On your userform create a new 'CommandButton' that will execute the macro you wish to trigger.
1) 在您的用户窗体上创建一个新的“命令按钮”，它将执行您希望触发的宏。
2) Set the height and the width of the button to 0
2）设置按钮的高度和宽度为0
3) Make sure the 'TabIndex' parameter for the button is 0... This will create an 'invisible' CommandButton that will receive the focus as soon as the form opens.
3) 确保按钮的“TabIndex”参数为 0...这将创建一个“不可见”命令按钮，该按钮将在窗体打开后立即接收焦点。
4) In the calling routine, immediately before the command that 'shows' the userform enter the line - 'Application.SendKeys "~"'
4) 在调用例程中，在“显示”用户表单的命令之前立即输入行 - 'Application.SendKeys "~"'
How it works...
这个怎么运作...
The commandbutton created in (1) is a valid control just like any other wxcept that you can't see it or click it with the mousebutton. The 'SendKeys' command replicates a left mouse key click which is stored in the keyboard buffer until the form displays when it will be read. This has exactly the same effect as a mouse click and will run the required macro.
在 (1) 中创建的命令按钮是一个有效的控件，就像任何其他 wxcept 一样，您看不到它或用鼠标按钮单击它。“SendKeys”命令复制鼠标左键单击，该单击存储在键盘缓冲区中，直到表单显示时将被读取。这与单击鼠标具有完全相同的效果，并将运行所需的宏。
Incidentally, if you are calling the macro from more than one location and wish to have different actions dependant on the source of the call, you can add more than one 'invisible' button and add "{Tab}" before the "~" character to tab the focus through the available controls. e.g. 'Application.SendKeys "{Tab}~"' will activate the button with the 'TabIndex' parameter set as 1. 'Application.SendKeys "{Tab}{Tab}{Tab}{Tab}~" ' will activate the button with the 'TabIndex' parameter set as 4 etc.
顺便说一句，如果您从多个位置调用宏并希望根据调用源执行不同的操作，您可以添加多个“不可见”按钮并在“~”字符前添加“{Tab}”通过可用的控件选项卡焦点。例如，'Application.SendKeys "{Tab}~"' 将激活按钮，'TabIndex' 参数设置为 1。'Application.SendKeys "{Tab}{Tab}{Tab}{Tab}~"' 将激活按钮'TabIndex' 参数设置为 4 等。
RF
射频
