VBA 子活动时未更新文本框
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/11989481/
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
Text boxes not being updated while VBA sub active
提问by Mike D
I have a large cell in sheet "main" containing several text controls. These are linked to named cells in sheet "Refs". There's a button in "main" that launches a VBA sub.
我在工作表“main”中有一个大单元格，其中包含几个文本控件。这些链接到工作表“Refs”中的命名单元格。“main”中有一个按钮可以启动 VBA 子程序。
At the start, the sub does...
一开始，sub...
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.Calculation = xlCalculationManual
then it updates the cells behind the names in sheet "Refs" Each update is surrounded by
然后它更新表“Refs”中名称后面的单元格每个更新都被包围
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
RefsSheet.Cells(row_downldstat, col_downldstat) = state
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
The VBA sub takes a while to finish so the idea is to provide feedback to the user as to what's going on. Unfortunately the text boxes are not updated until the VBA sub returns.
VBA sub 需要一段时间才能完成，因此我们的想法是向用户提供有关正在发生的事情的反馈。不幸的是，在 VBA 子返回之前，文本框不会更新。
If I put the formula =Down_State in a cell on the "main" sheet I see it updated as the VBA routine runs. Down_State is the same cell as RefsSheet.Cells(row_downldstat, col_downldstat). I prefer NOT to use this method as there are several textboxes and the whole idea was to migrate from such a design.
如果我将公式 =Down_State 放在“主”表的单元格中，我会看到它随着 VBA 例程的运行而更新。Down_State 是与 RefsSheet.Cells(row_downldstat, col_downldstat) 相同的单元格。我不喜欢使用这种方法，因为有几个文本框，整个想法是从这样的设计迁移。
Note that the textboxes are simply in a cell, not in a form.
请注意，文本框只是在一个单元格中，而不是在表单中。
I do know about the application.statusbar but that's not very noticeable.
我知道 application.statusbar 但这不是很明显。
How can I cause these text boxes to be updated while the VBA sub is active?
当 VBA 子处于活动状态时，如何更新这些文本框？
采纳答案by Siddharth Rout
Change your UpdateStatusto this.
改变你UpdateStatus的这个。
Sub UpdateStatus(state As String)
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
RefsSheet.Cells(row_downldstat, col_downldstat) = state
Wait 1
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Application.DisplayStatusBar = True
Application.StatusBar = state
End Sub
And add this new sub below the above.
并在上面添加这个新子。
Private Sub Wait(ByVal nSec As Long)
nSec = nSec + Timer
While nSec > Timer
DoEvents
Wend
End Sub
Now Test it ;)
现在测试它;)
回答by scott
I'm not sure if this is exactly what you're looking for:
我不确定这是否正是您要找的：
you said
你说
the idea is to provide feedback to the user as to what's going on
这个想法是向用户提供关于正在发生的事情的反馈
If you can not get the cells to update, perhaps creating a userform that comes up while the code runs could be useful.
如果您无法更新单元格，那么创建一个在代码运行时出现的用户表单可能会很有用。
here is a link to a type of userform that you could use, which simply displays a progress bar (this is most easily utilized when you are doing a lot of looping, but can be used in linear code as long as you don't care about the percentage accuracy)
这是您可以使用的一种用户表单类型的链接，它只显示一个进度条（这在您进行大量循环时最容易使用，但只要您不关心，也可以在线性代码中使用关于百分比准确度）
http://spreadsheetpage.com/index.php/tip/displaying_a_progress_indicator/
http://spreadsheetpage.com/index.php/tip/displaying_a_progress_indicator/
From the link:
从链接：
PctDone = Counter / (RowMax * ColMax)
With UserForm1
.FrameProgress.Caption = Format(PctDone, "0%")
.LabelProgress.Width = PctDone * (.FrameProgress.Width - 10)
End With
' The DoEvents statement is responsible for the form updating DoEvents
' DoEvents 语句负责表单更新 DoEvents
if you want you can edit the userform and the code to simply update the
如果你愿意，你可以编辑用户表单和代码来简单地更新
.frameprogress.caption =format(pctdone, "0%")
to
到
.frameprogress.caption = "String of desired text"
This is a way you can give the user feed back that a sub is running however it may not be optimal for your needs
这是一种您可以向用户反馈 sub 正在运行的方式，但它可能不是您需要的最佳选择
